daintysally
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?
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?
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
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
ASKER
I tried BriCrowe 's solution and there is an error on the first case statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked great!! Thank you!!
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 )