Nested Case Statement 1

[jebenson]

Hello all,

I am having some problems with a Case statement. Basically, if one field hits a certain value, then another field is evaluated:

CASE T.sCodeIDf_0
WHEN '7806' THEN
CASE T.curAmount
WHEN (T.curAmount > 0) THEN (t.curAmount*-1)
ELSE T.curAmount END
ELSE T.curAmount
END AS Amount

When I run this in SQL Query Analyzer I get the following error message:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '>'.

curAmount is a Currency field.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[PHV]

You may try something like this:
CASE WHEN T.sCodeIDf_0 = '7806' THEN
CASE WHEN T.curAmount > 0 THEN t.curAmount * -1
ELSE T.curAmount
END
ELSE T.curAmount
END AS Amount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jebenson]

Wow, a response time of four minutes! Thanks PHV, as usual your kung fu is mighty!

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson