[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

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?
0
daintysally
Asked:
daintysally
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 )
0
 
Brian CroweCommented:
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
0
 
daintysallyAuthor Commented:
I tried BriCrowe 's solution and there is an error on the first case statement.
0
 
Brian CroweCommented:
sry...air code.  I forgot parentheses around the IN values

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
0
 
daintysallyAuthor Commented:
This worked great!!  Thank you!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now