How to select decimal type field

Question

How to select decimal type field

kiasystemsoft

SSC Veteran

Points: 279
More actions
February 2, 2015 at 4:25 am

#314206

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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