asked on

Nested CASE/IF statements in MS SQL Server 2000

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!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rschmalt

ASKER

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]

indeed... sorry :)

rschmalt

ASKER

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. ;)

jboby

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

Vadim Rapp

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.