February 2, 2015 at 4:25 am
hi
Excuse me
I dont speek english well
I have a table with fields:
Id int
Pay numeric(19,3)
value records:
id pay
1 1.000
2 2.250
3 3.445
4 6.000
I want select of table to form:
id pay
1 1
2 2.25
3 3.445
4 6
if value decimal pay field Greater of zero then
value select= value field
else
delete value decimal and show
tanks.
February 2, 2015 at 5:44 am
you would typically do formatting in the application, and not in the database.
because you want to mix data types, ie decimal(19,3) and integer , you need to either format as a varchar, or maybe switch to using a sql_variant.
i use sql_variant so rarely, i don't think i have a decent example of it's usage.
Lowell
February 2, 2015 at 3:28 pm
You'll have to use varchar output rather than a numeric format:
SELECT REPLACE(CAST(pay AS varchar(30)), '.000', '') AS pay,
...
FROM table_name
WHERE ...
...
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 2, 2015 at 5:34 pm
Lowell (2/2/2015)
...maybe switch to using a sql_variant.
What a brilliant idea!
WITH SampleData (ID, Pay) AS
(
SELECT 1, CAST(1.000 AS NUMERIC(19,3))
UNION ALL SELECT 2,2.250
UNION ALL SELECT 3,3.445
UNION ALL SELECT 4,6.000
)
SELECT ID, Pay
,PayFormatted=CASE WHEN FLOOR(Pay) = Pay THEN CAST(CAST(Pay AS INT) AS SQL_VARIANT) ELSE Pay END
FROM SampleData;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply