Nested CASE statements and logical constructs 1

[wbodger]

I think that is the subject that I want...

Here is the deal. I am at a new job, doing some fun stuff with SQL, different than I have had to do before and am therefore asking a lot of questions (thank you for your help!). Now, on to the question. I have to do some logical flow control, which I simply did with CASE statements when there were only 2 questions, but now there are 6 and I wanted to see if anybody out there knew of a cleaner way. What I have done for 2

WHEN sb274.SpecimenStoredID=66 AND pi274.DE385_DOTIDName='Yes' AND pi274.DE386_Centrifuged20IDName='Yes' THEN 1
WHEN sb274.SpecimenStoredID=66 AND pi274.DE385_DOTIDName='No' AND pi274.DE386_Centrifuged20IDName='No' THEN 0
WHEN sb274.SpecimenStoredID=66 AND pi274.DE385_DOTIDName='No' AND pi274.DE386_Centrifuged20IDName='Yes' THEN 0
WHEN sb274.SpecimenStoredID=66 AND pi274.DE385_DOTIDName='Yes' AND pi274.DE386_Centrifuged20IDName='No' THEN 0
ELSE 9

For all of the questions on the form, there are permissible values, so I can at least verify against known values. Now that it is up to 6 questions/criteria

question                                                req answer
parentvalue		                                1
Removed without disturbing pellet	                yes	
time from collection to storage within 0-18 hours	0-18*	
Processed according to protocol	                        yes
Was there an equipment failure/deviation from protocol	no	
Stored at -70C or colder	                        yes

I am thinking there must be a better way than trying to handle all of the questions such as I have done with just 2 questions. In essence, what I am doing is saying whether or not the specimens were handled according to SOPs given the answers to 2 to 5 questions. If the SOP was followed/met for each criteria, then it gets a 1. If any of them are a no, then it gets a 0 UNLESS any of them are missing data, in which case it gets a 9. There is also the situation where there are parent specimens and child specimens and the parent specimens SOP status has to be considered when determining the child specimen status. I have thought thru a few different ways to do this, but have not come up with an answer that I feels best fits the situation. I had started with creating a view for the SOP status of the parent specimens in which I have the specimen id and the SOP status. I was then going to create another view with more of the same logic for the child specimens when I ran into a larger number of variables and I realized that I had to rethink my approach, which brought me here...

wb

 

[simian336]

Are you wanting to run this process on an entire table and update a field?

This is probably better done thru the front end but I would just create a field like STATUS. Then update that with your whens.

update field
set status = 1
wheresb274.SpecimenStoredID=66 AND pi274.DE385_DOTIDName='Yes' AND pi274.DE386_Centrifuged20IDName='Yes'

update field
set status = 0
where sb274.SpecimenStoredID=66 AND (pi274.DE385_DOTIDName='No'
or pi274.DE386_Centrifuged20IDName='No')

and ELSE 9 is not really an option sinse there is not other combo here.

Then do similar conditions for each making sure the order covers your sinarios.

Simi

 

[wbodger]

That is what I have done with CASE statements. The ELSE does have to be there, because there will be times when they do not answer the question and if a question has not been answered then it gets a 9. I was looking to see if anybody had a method that would less brute force, or if that is even possible. Once it gets up to 5 questions, that is a lot of combinations...

 

[lameid]

In the OP I see three solutions and 5 tests (4 whens and else). One Test is true (1), the exception is 9 for whatever reason and everything else is false (0). You defined your exception differently than the tests.... I suspect you can test your values not being answered with IS null.... So you have a true case, an exception case and an else case of false.

That is unless I am missing something fundamental.

 

[wbodger]

No, I think you are correct. I was just talking thru this with a colleague and I think that I can say:
1) If everything is true then 1
2) If ANYthing is null then 9
3) else 0
Which is, I believe, the same thing you just said. This does not save me much on the simple cases, but when there are 5 or 6 questions this saves me a lot of code.

Now, in that same vein, does anybody know if there is any way in SQL to do the equivalent of
If column1=column2='Yes' Then something
other than
If column1='Yes' AND column2='Yes' Then something

wb

 

[gmmastros]

Maybe something like this....

Check each column separately.

1. If the answer matches, the return 0.
2. If the answer is null, then null
3. if the answer does not match then 1.

Then... add up the results from each question.

1. If the result is null, then there is at least one answer that is null.
2. If the result is 0, then everything matches, return 1.
3. Else, return 0.

[tt]
Select Case When SumOfResults Is NULL Then 9
When SumOfResults = 0 Then 1
Else 1 End
From (
Select Case When Column1 = 'Yes' Then 0
When Column1 IS NULL Then NULL
Else 0 End
+ Case When Column2 = 'Yes' Then 0
When Column2 IS NULL Then NULL
Else 1 End
+ Case When Column3 = 'Yes' Then 0
When Column3 IS NULL Then NULL
Else 1 End As SumOfResults
From YourTable
) As AliasName
[/tt]

Basically, I suggest that you test each one separately. Add the results together, and then set the final output accordingly.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Did this help? Does it make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Sorry, yes. I saw it as I was getting ready to leave. I have it implemented in one view, need to look at the more involved view today, but I am pretty sure it will work wonderfully and save me a lot of code. Thanks!

 

[JonFer]

Looks like most of the "yes" questions indicate things were processed normally and without problems except the question about deviating from protocol where a "yes" would mean a problem. Is that the intent of the "1" for all questions = "yes" to be everything is okay with that sample or whatever?

Modifying gmmastros suggestion slightly you could multiply the results of each case to give the final 0, 1 or Null result directly.

Select SpecimenID,
(Case When Column1 = 'Yes' Then 1
When Column1 IS NULL Then NULL
Else 0 End
*
Case When Column2 = 'Yes' Then 1
When Column2 IS NULL Then NULL
Else 0 End) as Result