Nested CASE/IF statements in MS SQL Server 2000

**Anilrgowda** · 22-Oct-2007, 12:45 AM

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"

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

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

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

**Anilrgowda** · 22-Oct-2007, 12:45 AM

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

22-Oct-2007, 12:45 AM	#2 (permalink)
Anilrgowda Administrator Posts: 18,715 Join Date: Jan 2006 Rep Power: 10 IM:	Re: Nested CASE/IF statements in MS SQL Server 2000 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 ------------------

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)