case when problem in query

Question

Post reply

case when problem in query

Jeff-132889

Ten Centuries

Points: 1112
More actions
December 29, 2006 at 10:26 am

#115547

I'm trying to evaluate several columns and have the answer populate a single column:
case when col1 = 1 THEN 'yes' else (when col2 = 1 THEN 'no') end as column3
I cannot get this to work and presume it's logic rather than sytax.

Thanks in advance.......................

Viewing 7 posts - 1 through 6 (of 6 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

This doesn't match the syntax from BOL, for example:

CASE ProductLine

WHEN 'R' THEN 'Road'

WHEN 'M' THEN 'Mountain'

WHEN 'T' THEN 'Touring'

WHEN 'S' THEN 'Other sale items'

ELSE 'Not for sale'

END,

So, maybe you could state the rules in English and we could help structure this. Are col1 and col2 mutually exclusive? Could you have a case where clo1 = 1 and col2 = 1? If so, what should the value then become? You might be able to do something like:

case col1

when 1 then 'yes'

else case col2

when1 then 'no'

end

What do you want to have happen when neither condition is true (or is that possible)?

And then again, I might be wrong ...
David Webb

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

nested case statements look like this

case when col1 = 1 then 'yes' else

case when col2 = 1 then 'no' else

'maybe'

end

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 3

Thanks mrpolecat I was simply missing the correct number of 'end' statements..............

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

this may be cleaner

case when col1 = 1 then 'yes'

case when col2 = 1 then 'no'

else

'maybe'

end

note that if a condition is met then it stops looking so if col1 and col2 are 1 then you get 'yes'

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Actually, there is one CASE too much in your SQL (probably just a typo):

CASE WHEN col1 = 1 THEN 'yes'

~~case~~ WHEN col2 = 1 THEN 'no'

ELSE 'maybe'

END

kiruba-370649 Old Hand Points: 372 More actions · Answer 6

Jeff,

You have to try like this

case when col1 = 1 THEN 'yes' else (case when col2 = 1 THEN 'no' end) end as column3

Regards

Kiruba Sankar.S