Simple CASE statement help! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Simple CASE statement help!


In the database, the StatisticValue is stored as numeric(18,4) and the flag is 1. I want to retrieve this data with case like this: select zst.StatisticID,
CASE StatisticValue
WHEN IntegerFlag = 1 THEN cast(StatisticValue as int)
WHEN IntegerFlag = 0 THEN cast(StatisticValue as numeric(18,4))
ELSE’Unknown’
END AS StatisticValue
fromSCHOOL_STATISTIC ss
joinzSTATISTIC_TYPE zst
onss.StatisticID = zst.StatisticID Getting a error message: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘=’.
Thanks
Dan
Get rid of the StatisticValue after case. Seems like you are writing it kind of like a switch statement and that is causing your error. select zst.StatisticID,
CASE WHEN IntegerFlag = 1 THEN cast(StatisticValue as int)
WHEN IntegerFlag = 0 THEN cast(StatisticValue as numeric(18,4))
ELSE ‘Unknown’
END AS StatisticValue
from SCHOOL_STATISTIC ss
join zSTATISTIC_TYPE zst
on ss.StatisticID = zst.StatisticID
John

John,
Thanks for the reply. The syntax looks ok but didn’t get the desired result. This is how I did it: case IntegerFlag when 1 then cast(StatisticValue AS int) else NULL end as statisticvalue,
case IntegerFlag when 0 then StatisticValue else NULL end as statisticvalue,
Thanks,
Dan
You might mean this:
case integerflag
when 1 then cast(StatisticValue AS int)
when 0 then StatisticValue
else null
end


It’s not getting the output as int. It gets as numeric which I do not want. I want the result to be int when I specify IntegerFlag = 1. Thanks for your suggestion. Dan
If you have both int and numeric data coming out of this, you aren’t going to get a final resultset that’s part int and part numeric. This is because each data column on output must be a consistent or same datatype. You are especially in trouble if you want to ever mix the numeric and alphas. Here’s some code that will demonstrate for you what how SQL Server handles data type combinations and output. Look at each of them carefully, as they tell several different stories that are important if you’ll be using SQL Server a lot. I would also study data types in BOL.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @test TABLE(col1 NUMERIC(16,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,IsInteger INT)<br /><br />INSERT @test(col1, IsInteger)<br />SELECT 123213,1 UNION ALL<br />SELECT 123,1 UNION ALL<br />SELECT 1.12445,0 UNION ALL<br />SELECT 42555,1 UNION ALL<br />SELECT 12.14154,1 <br /><br />SELECT col1, IsInteger<br />FROM @test<br /><br />SELECT<br />CASE<br />WHEN IsInteger = 1 THEN CAST(col1 AS INT)<br />WHEN IsInteger = 0 THEN CAST(col1 AS NUMERIC(18,4))<br />END AS col1,<br />IsInteger<br />FROM @test<br /><br />SELECT col1, IsInteger FROM @test WHERE IsInteger = 1<br />UNION ALL<br />SELECT col1, IsInteger FROM @test WHERE IsInteger = 0<br /><br />SELECT<br />CASE<br />WHEN IsInteger = 1 THEN CAST(CAST(col1 AS INT) AS VARCHAR(55))<br />WHEN IsInteger = 0 THEN CAST(CAST(col1 AS NUMERIC(18,4)) AS VARCHAR(55))<br />END AS col1,<br />IsInteger<br />FROM @test<br />UNION ALL<br />SELECT ‘WHAT????’,3<br /></font id="code"></pre id="code"><br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Thanks everyone for the reply. Since I only have the IntegerFlag of 1 right now, I can get the int data by using the following condition which is needed for the xml output right now. Will look into it more when I have different flag value in the database. Thanks case IntegerFlag when 1 then cast(StatisticValue AS int) else NULL end as statisticvalue
Dan
]]>