NESTED Statement only compiles part of first CASE

Question

NESTED Statement only compiles part of first CASE

Tammy Robinson

Mr or Mrs. 500

Points: 567
More actions
February 5, 2010 at 8:39 am

#98536

I have the following CASE statement. I want to be able to put multiple values in one column on a report. The user has the option of using one 'property' or multiple 'properties'. However, when I use this statement the end result only captures the very first property in the list along with the remaining statement. It skips the 'prop2' and 'prop3' in the parenthesis. It should add all of the first statement to the other remaining statements.
EXample:
(Case When property in ('prop1','prop2','prop3') Then 1
When property = 'prop2' Then 2
When property = 'prop3' Then 3
....
ELSE 0 END)
The result should be prop1 + prop2 + prop3 + prop2 + prop3 which would be 11.
Is this possible? What am I doing wrong? I have 10 statements but only 1 case.
Any help is appreciated.

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

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

Tammy from what you describe, the CASE statement is working as it is supposed to, but not the way you want it to.

a case statement executes the first TRUE condition it finds, and then exits...the other WHEN statements are not even evaluated if a preceeding WHEN section is satisfied:

(Case When property in ('prop1','prop2','prop3') Then 1

When property = 'prop2' Then 2 --Because 'prop2' is found in the first, this is never evaluated

When property = 'prop2' Then 5 --Because 'prop2' is found in the first, this is never evaluated

When property = 'prop2' Then 9 --Because 'prop2' is found in the first, this is never evaluated

When property = 'prop2' Then 7 --Because 'prop2' is found in the first, this is never evaluated

When property = 'prop3' Then 3 --Because 'prop3' is found in the first, this is never evaluated

....

ELSE 0 END)

show us your full query, and the expected output; i'm thinking if you are returning a comma delimited list(is that right?) you'll want to use the FOR XML trick to generate that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 2

doesnt the first line satisfy all potential outcomes?

if 'property' = 'prop2' then it will be 'IN ('prop1','prop2','prop3')'

or have a misread?

Tammy Robinson Mr or Mrs. 500 Points: 567 More actions · Answer 3

Thank you both for the replies.

I have not explained it very well, but I think now I understand what the Case statement is doing. It really is doing what it should.

The user could have more than one property in one column. The idea would be for the rest of the columns (there are 9 others) to have a DIFFERENT property. Then these properties would be used in a calculation of some sort (it could be anything) into the ending column. It works just as it should. I was just repeating my properties which caused it to stop at the first CASE.

Thanks for making me see the error in my thinking. I really appreciate it.

Lowell SSC Guru Points: 323507 More actions · Answer 4

maybe something like this will help you model your calculation?

SELECT

SUM(

CASE WHEN property = 'prop1' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop2' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop3' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop4' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop5' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop6' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop7' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop8' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop9' THEN 1 ELSE 0 END

+ CASE WHEN property = 'prop10' THEN 1 ELSE 0 END

) As TheTotal

From SomeTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tammy Robinson Mr or Mrs. 500 Points: 567 More actions · Answer 5

Tammy Robinson

Mr or Mrs. 500

Points: 567

February 5, 2010 at 10:05 am

#1114588

Thanks Lowell.

I will give it a try.