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!Start Free Trial