Link to home
Start Free TrialLog in
Avatar of daintysally
daintysallyFlag for United States of America

asked on

Nested case statement

Hi experts,

I am  trying to write a nested case statement and I am getting an error.  Please see a sample of what I am trying to do below:

Case when dbo.field1 in ('val1','val2') then 'test1'
When dbo.field1= 'val3' then 'test2'
Else 'blah'
End Newfieldname

Then I need to use the 'Newfieldname' in another case statement to create another field.

Case when 'Newfieldname'  in ('test1','test2') then 'Yes'
End Newfieldname


How can I get this done?
Avatar of Aneesh
Aneesh
Flag of Canada image

You have to use something like tghis

SELECT  Case when 'Newfieldname'  in ('test1','test2') then 'Yes'
End Newfieldname

       
FROM (
SELECT
Case when dbo.field1 in ('val1','val2') then 'test1'
When dbo.field1= 'val3' then 'test2'
Else 'blah'
End Newfieldname
FROM TableA )
You either need to duplicate the first case statement within the second case statement or you need to include the first case statement in a subquery.

CASE
   WHEN
      CASE
         WHEN dbo.field1 in ('val1','val2') then 'test1'
         WHEN dbo.field1= 'val3' then 'test2'
         ELSE 'blah'
      END IN 'test1', 'test2' THEN 'YES'
   ELSE 'No'
END AS Newfieldname
Avatar of daintysally

ASKER

I tried BriCrowe 's solution and there is an error on the first case statement.
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This worked great!!  Thank you!!