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?
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

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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!!!
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Jim left out the WHEN in line 2 of each of his code snippets.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
corrected
0
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?
0
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.
0
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

0
Mike EghtebasDatabase and Application DeveloperCommented:
To modify, let us know what code you are using now.
0
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

0
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

0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That worked! Thanks again for your help! I'll get these statements sooner or later! :)
0
QlemoBatchelor, 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

0
Mike EghtebasDatabase and Application DeveloperCommented:
@Qlemo, thanks.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.