?
Solved

Nested CASE/IF statements in MS SQL Server 2000

Posted on 2007-07-20
7
Medium Priority
?
46,968 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!
0
Comment
Question by:rschmalt
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 19530791
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

0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 100 total points
ID: 19530808
======
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
=====
0
 

Author Comment

by:rschmalt
ID: 19530930
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19531031
indeed... sorry :)
0
 

Author Comment

by:rschmalt
ID: 19531058
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.  ;)
0
 

Expert Comment

by:jboby
ID: 20995396
if using sql 2000, i would also suggest creating a user defined function to handle the case statement.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 20995585
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question