Need help with converting into decimals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with converting into decimals

Author

Topic

raghav_sai2002
Starting Member

18 Posts

Posted - 2009-09-17 : 09:16:30

hi,

i have to form a query, in which i need to fetch a particular column in decimal format.

but the problem i am facing is the format needs to be done using a case statement.. depending upon the another column...

i can give an example of the scenario
CREATE TABLE #tmp1
(
ID INT Primary KEY IDENTITY,
Value1 DECImal(10,4)
)

insert into #tmp1 Values( 10.1)
insert into #tmp1 Values( 10.12)
insert into #tmp1 Values( 10.123)
insert into #tmp1 Values( 10.1234)

select ID,
CASE WHEN ID = 1 THEN Convert( decimal(10,1), Value1)
WHEN ID = 2 THEN Convert( decimal(10,1), Value1)
ELSE Convert( decimal(10,3), Value1) END AS VALUE1

from #tmp1

eventhough i am specifying a specific conversion in the case, i still get all the results in decimal(10,3) format as it is the max length value.

please do help me out on this one.

thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 09:38:42

see http://msdn.microsoft.com/en-us/library/ms181765.aspx

quote:

Return Types

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 09:39:01

You cannot have one column in the result set that should have different data types in each row.
The column needs same data type in every row.

A workaround could be to convert it finally to varchar

No, you're never too old to Yak'n'Roll if you're too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-17 : 09:44:26

[code]SELECT ID,
CASE WHEN ID = 1 THEN STR(Value1, 10, 1)
WHEN ID = 2 THEN CAST(Value1 as DECIMAL(10,1))
ELSE CONVERT( DECIMAL(10,3), Value1) END AS VALUE1
FROM #tmp1
[/code]
sorry can't help you...tried everything

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-17 : 09:44:45

omg!

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 10:02:27

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

raghav_sai2002
Starting Member

18 Posts