Casting the number of decimals in a select dynamically (For eg, amounts based on the number of decimals of the currency)

Question

Casting the number of decimals in a select dynamically (For eg, amounts based on the number of decimals of the currency)

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Agreed... and thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

narensantanam SSC Journeyman Points: 99 More actions · Answer 2

Jeff & Magoo,

Thanks a ton for the help and the suggestions. Will validate and then apply this code.

Regards

Naren

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Thanks for the feedback, Naren... especially about the validation part. I'd hate to see someone get into trouble because of something like rounding.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 4

The reason why the code in your first sample will not give you the expected results is your ELSE condition:

since your ELSE condition is of data type DECIMAL, SQL Server will convert all results of your CASE statement to DECIMAL with the same scale value as the one you defined. For details, please see BOL, section "Data Type Precedence (Transact-SQL)". If you change the data type of @input to FLOAT it seems to give you the requested output. But it's still a numeric value. You can easily check that by trying to add a text to the result of the case statement.

You'd get your desired output (at least in terms of decimals displayed) when changing the ELSE condition to return a VARCHAR as well:

Using your sample data:

SELECT

CASE WHEN @decs = 2 THEN CAST(CAST(@input AS DECIMAL(17,2)) AS VARCHAR(19))

WHEN @decs = 1 THEN CAST(CAST(@input AS DECIMAL(17,1)) AS VARCHAR(19))

ELSE CAST(@input AS VARCHAR(19)) -- + 'test string'

END

This doesn't resolve the rounding issue at all. But that's not the point I'm trying to make (the issue is well covered already). My goal is to show you the reason why your original query didn't give you the desired output.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]