Another Nested CASE Statement

I'm still not sure how to write nested CASE WHEN statements. I have another one that I need help with.

Here is the criteria: IF J1.SOKey IS NOT NULL Then '01' ELSE IF J1.SOKey IS NULL and J2.RMPartNo IS NOT NULL then '04' AND IF J1.SOKey AND J2.RMPartNo are BOTH NULL then A2.fprodcl

Any help would be appreciated. I'm learning these but still have some trouble with the nested ones.
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
see if this work for you

Case When IsNull( J1.SOKey ,'')='' AND IsNull(J2.RMPartNo,'')='' Then A2.fprodcl
When IsNULL( J1.SOKey,'')<>'' Then '01'
Else
 '04' 
End

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Here you go.  Helps to visualize if you indent (normally tab, in below code block three spaces)
CASE 
   WHEN J1.SOKey IS NOT NULL THEN '01' 
   ELSE 
      CASE 
         WHEN J1.SOKey IS NULL AND J2.RMPartNo IS NOT NULL THEN '04' 
         WHEN J1.SOKey IS NULL AND IS NULL AND THEN A2.fprodcl
         END
   END as column_name

Open in new window

Since if J1.SOKey is NULL then it would fall into the second CASE, then we can lose testing J1.SOKey for NULLs...
CASE 
  WHEN J1.SOKey IS NOT NULL THEN '01' 
   ELSE 
      CASE 
         WHEN J2.RMPartNo IS NOT NULL THEN '04' 
         WHEN J2.RMPartNo IS NULL THEN A2.fprodcl
         END
   END as column_name

Open in new window


Then since the last line (J2.RMPartNo IS NULL) is opposite of the one above it, just use ELSE
CASE 
   WHEN J1.SOKey IS NOT NULL THEN '01' 
   ELSE CASE WHEN J2.RMPartNo IS NOT NULL THEN '04' ELSE A2.fprodcl END
   END as column_name

Open in new window

Also, since we're setting values to '01' and '04', this implies that A2.fprodcl is some kind of character.
If it's numeric, change '01' and '04' to 1 and 4.

Also, I have an article out there called SQL Server CASE Solutions that illustrates a lot of this.

Hope this helps.
Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin SparksCommented:
Try laying them out in a indented layout. It makes it easy to follow

CASE  WHEN J1.SOKey IS NOT NULL  THEN '01'
           WHEN  J1.SOKey IS NULL THEN
                     CASE WHEN J2.RMPartNo IS NOT NULL THEN '04'
                               WHEN  J2.RMPartNo IS NULL THEN A2.fprodcl
                      END
END

Kelvin
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you both for your quick responses! I always appreciate the quick responses and detailed explanations! I will learn these soon enough! And thanks Jim for the link. Always helpful!!!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Jim left out the WHEN in line 2 of each of his code snippets.
Jim HornMicrosoft SQL Server Data DudeCommented:
corrected
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Just out of pure curiosity, why wouldn't they use the IIF statements like they do in MS Access? Why use a CASE WHEN statement to substitute the simple, and very easy to understand IIF statements?
Mike EghtebasDatabase and Application DeveloperCommented:
You can use iif() like in access if it is SQL Servr 2012 and up. But generally for portability purpose, programmers prefer to use standard SQL (in this case Case...When...Then...Else...End). This way your code will run the same in SQL Server as well as Oracle.

iif() is specific to T-SQL.

Mike
--------

@Qlemo,

Thank you for posting an alternate solution for another case related question I had answered. I learned from it.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I messed up with my original question. There were 2 other options that I should have included in my CASE WHEN statement. I need to add these steps into the CASE WHEN statement.

IIF A2.fprodcl = '03' THEN '03' AND IIF A2.fprodcl = '02' THEN '02'.

I need to add these to the CASE WHEN statement answers from this post. Is it simple enough to just add this code to the beginning of the other CASE WHEN's?

CASE WHEN A2.fprodcl = '03' THEN '03' ELSE CASE WHEN A2.fprodcl = '02' THEN '02'

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
To modify, let us know what code you are using now.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
This is what I tried to do but it's giving me an error saying "Incorrect syntax near the keyword 'AS'"

CASE WHEN A2.fprodcl = '03' THEN '03' ELSE CASE WHEN A2.fprodcl = '02' THEN '02' ELSE CASE WHEN IsNull(J1.SOKey, '') = '' AND IsNull(J2.RMPartNo, '') = '' THEN '04' WHEN IsNULL(J1.SOKey, '') <> '' THEN '01' ELSE '01' END AS NewProdCL

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
try:
CASE 
   WHEN (A2.fprodcl = '03' OR A2.fprodcl = '02') THEN A2.fprodcl
   WHEN IsNull( J1.SOKey ,'')='' AND IsNull(J2.RMPartNo,'')='' Then A2.fprodcl
   WHEN IsNULL( J1.SOKey,'')<>'' Then '01'
   Else '04' 
End

Open in new window

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That worked! Thanks again for your help! I'll get these statements sooner or later! :)
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
A slightly different way:
CASE 
   WHEN A2.fprodcl  in ('02', '03') THEN A2.fprodcl
   WHEN IsNull( J1.SOKey ,'')='' AND IsNull(J2.RMPartNo,'')='' Then A2.fprodcl
   WHEN IsNULL( J1.SOKey,'')<>'' Then '01'
   Else '04' 
End

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
@Qlemo, thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.