We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Nested CASE/IF statements in MS SQL Server 2000

Medium Priority
47,790 Views
Last Modified: 2009-10-02
I'm writing a SQL Query in VB2003 against an MS SQL Server 2000 database.  Basically, I"m trying to replicate an Access query (which works) in MS SQL language.  The problem is, I can't seem to make a nested CASE statement work.

Here is my original query:
[code]                  SELECT Left(dbo_porel.jobnum,5) AS ShortJobNum,
                        IIf(dbo_porel.jobseqtype="M",IIf(Len(nz(dbo_partclass.shortchar01,""))=0,"Unassigned",dbo_partclass.shortchar01),
                        IIf(Len(nz(dbo_opmaster.shortchar01,""))=0,"Unassigned",dbo_opmaster.shortchar01)) as Headers,
                        (dbo_porel.relqty*dbo_podetail.unitcost) AS Cost

                  FROM (((dbo_porel
                        INNER JOIN dbo_podetail ON (dbo_porel.ponum = dbo_podetail.ponum) AND (dbo_porel.poline = dbo_podetail.poline))
                        LEFT JOIN dbo_joboper ON (dbo_porel.jobnum = dbo_joboper.jobnum) AND (dbo_porel.assemblyseq = dbo_joboper.assemblyseq) AND (dbo_porel.jobseq = dbo_joboper.oprseq))
                        LEFT JOIN dbo_opmaster ON dbo_joboper.opcode = dbo_opmaster.opcode)
                        LEFT JOIN dbo_partclass ON dbo_podetail.classid = dbo_partclass.classid

                  WHERE Left(dbo_porel.jobnum,5) Between "36640" And "36646" [/code]

Specifically, I'm dealing with this line:
[code]
IIf(dbo_porel.jobseqtype="M",IIf(Len(nz(dbo_partclass.shortchar01,""))=0,"Unassigned",dbo_partclass.shortchar01),
                        IIf(Len(nz(dbo_opmaster.shortchar01,""))=0,"Unassigned",dbo_opmaster.shortchar01)) as Headers[/code]

I'm trying to rewrite this in something MS SQL will luck and frankly, I"ve been without much success.  My latest attempt was as follows:

[code]
      Headers = Case
            WHEN porel.jobseqtype='M' THEN
                  (WHEN Len(IsNull(partclass.shortchar01,''))=0 THEN 'Unassigned'
                  Else partclass.shortchar01)
            ELSE
                  (WHEN Len(IsNull(opmaster.shortchar01,''))=0 THEN 'Unassigned'
                  ELSE opmaster.shortchar01)
            End[/code]

I'd sure appreciate someone's assistance on this.  All my searches so far have not indicated how to do the following:

If Value
    THEN
            IF Value
                 THEN
            ELSE
                 THEN
     ELSE
            IF Value
                 THEN
            ELSE
                 THEN
END IF

Thank you!
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
you are were close:

 Headers = CASE
            WHEN porel.jobseqtype='M'
            THEN  WHEN Len(IsNull(partclass.shortchar01,''))=0
                  THEN 'Unassigned'
                  ELSE partclass.shortchar01
                  END
            ELSE  WHEN Len(IsNull(opmaster.shortchar01,''))=0
                  THEN 'Unassigned'
                  ELSE opmaster.shortchar01
                  END
            END

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Commented:
======
If Value
    THEN
            IF Value
                 THEN
            ELSE
                 THEN
     ELSE
            IF Value
                 THEN
            ELSE
                 THEN
END IF
====

->
====
case
    when value
     then case
              when value
                 then
                 else
              end
    else case
              when value
                 then
                 else
              end
end
=====

Author

Commented:
Headers = CASE
     WHEN porel.jobseqtype='M'
          THEN CASE
                 WHEN Len(IsNull(partclass.shortchar01,''))=0
                       THEN 'Unassigned'
                       ELSE partclass.shortchar01
                 END
          ELSE  CASE
                 WHEN Len(IsNull(opmaster.shortchar01,''))=0
                       THEN 'Unassigned'
                       ELSE opmaster.shortchar01
                  END
END

----------------

This code works!  Thanks!  AngelIII - you got me 99% of the way there, but vadimrapp1 's actual syntax is what saved me (angelIII's code was missing CASE).

How do I assign the points?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
indeed... sorry :)

Author

Commented:
Nothing to be sorry about.  :)  LIke I said, you got me 99% of the way there.  I'm just not sure how to assign the points considering I'm pretty new at this.  ;)

Commented:
if using sql 2000, i would also suggest creating a user defined function to handle the case statement.
CERTIFIED EXPERT

Commented:
user-defined functions can have _very_ bad impact on the performance of the query, so they have be used with big caution, especially so for something simple.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.