Question About Case Statement With This Query

[lrdave36]

Hey guys,


First, let me describe what I want to accomplish. I have a table that looks like this:


USER ID SCREEN Inquiry Change Delete
_______ ________ _______ ______ ______

2345 PR23 I C D



I want a query to display the word "ALL" if the users have I, C and D permissions.

Is this possible to do with a Case statement? Something like

CASE WHEN Inquiry = 'I" THEN 'ALL'

The problem is how can I do a CASE for multiple values? 'ALL' is not valid if they only have 'I'. Can you do nested case statements??

 

[r937]

nested CASE expressions are possible, but not needed in this, um, case

SELECT CASE WHEN Inquiry = 'T'
             AND Change = 'C'
             AND Delete = 'D'
            THEN 'All'
            ELSE NULL END  AS All

please note that DELETE is a reserved word, so you might need to escape it



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon