I'm looking for assistance using multiple CASE statements in an SQL query.

I have an SQL query that returns sales order data from our system.  However, many of the field values are integers, which do not make much sense to my end users.  I'd like to replace these values with text to help the users understand the report.

I understand that I will need to use a CASE statement to accomplish this, I can make it work for any one field, provided I have it at the end of my SELECT lines.  However, when I attempt to use CASE for more than one of the SELECTED fields, I receive a syntax error.

The following fields need to be translated from integer to text:

ST.SalesType (type of sales order)
3 = Sales Order
4 = Return Order

ST.SalesStatus (order status)
1 = Open Order
2 = Delivered
3 = Invoiced
4 = Canceled

WMS.ExpeditionStatus
3 = Activated Pick
4 = Pick Started
10 = Pick Complete
20 = Pick Canceled



My actual SQL query is as follows:

SELECT
        ST.SALESSTATUS,
      ST.SALESID,
      ST.SALESTYPE,
      ST.SALESNAME,
      SL.LINENUM,
      SL.ITEMID,
      SL.SALESQTY,
      SL.REMAINSALESPHYSICAL,
      ST.CUSTACCOUNT,
      ST.INVOICEACCOUNT,
      ST.CUSTOMERREF,
      ST.PURCHORDERFORMNUM,
      ST.DOCUMENTSTATUS,
      WMS.EXPEDITIONSTATUS,
      ST.DLVMODE,
      ST.CREATEDDATETIME,
      ST.SHIPPINGDATECONFIRMED,
      ST.SHIPPINGDATEREQUESTED      
            
FROM dbo.SALESTABLE ST
      INNER JOIN dbo.SALESLINE SL ON ST.SALESID = SL.SALESID
      INNER JOIN dbo.WMSPICKINGROUTE WMS ON ST.SALESID = WMS.TRANSREFID
WHERE ST.SalesStatus = 1


The return looks good (having trouble with formatting, else I'd put the part of the return here), other than what I mentioned above, where the values for key columns are numeric.

I added the following CASE statement to translate the data in the ST.SalesStatus column


'Order Status' = CASE
      WHEN ST.SALESSTATUS = 1 THEN 'Open Order'
      WHEN ST.SALESSTATUS = 2 THEN 'Order Delivered'
      WHEN ST.SALESSTATUS = 3 THEN 'Order Invoiced'
      WHEN ST.SALESSTATUS = 4 THEN 'Order Canceled'
END


However, when I placed it below ST.SALESSTATUS in the SELECT portion of my query, I receive a syntax error immediately after the END.  If I move the ST.SALESSTATUS  to the end of the SELECT, it works.  However, if I add CASE statements for the other SELECTED items, I receive syntax errors again.

I assume that I'm doing something wrong with the multiple SELECTS.  Can anyone point me in the right direction?


Thanks in advance.

Scott
Scott MilnerApplication AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
You are far, FAR better off using related tables to handle these instead of CASE statements.  That said:

SELECT
      CASE ST.SALESSTATUS
            WHEN 1 THEN 'Open Order'
            WHEN 2 THEN 'Delivered'
            WHEN 3 THEN 'Invoiced'
            WHEN 4 THEN 'Canceled'
            ELSE NULL
      END AS SALESSTATUS,
      ST.SALESID,
      CASE ST.SALESTYPE
            WHEN 3 THEN 'Sales Order'
            WHEN 4 THEN 'Return Order'
            ELSE NULL
      END AS SALESTYPE,
      ST.SALESNAME,
      SL.LINENUM,
      SL.ITEMID,
      SL.SALESQTY,
      SL.REMAINSALESPHYSICAL,
      ST.CUSTACCOUNT,
      ST.INVOICEACCOUNT,
      ST.CUSTOMERREF,
      ST.PURCHORDERFORMNUM,
      ST.DOCUMENTSTATUS,
      CASE WMS.EXPEDITIONSTATUS
            WHEN 3 THEN 'Activated Pick'
            WHEN 4 THEN 'Pick Started'
            WHEN 10 THEN 'Pick Complete'
            WHEN 20 THEN 'Pick Canceled'
            ELSE NULL
      END AS EXPEDITIONSTATUS,
      ST.DLVMODE,
      ST.CREATEDDATETIME,
      ST.SHIPPINGDATECONFIRMED,
      ST.SHIPPINGDATEREQUESTED      
            
FROM dbo.SALESTABLE ST
      INNER JOIN dbo.SALESLINE SL ON ST.SALESID = SL.SALESID
      INNER JOIN dbo.WMSPICKINGROUTE WMS ON ST.SALESID = WMS.TRANSREFID
WHERE ST.SalesStatus = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott MilnerApplication AdministratorAuthor Commented:
ok... it looks like all I was doing wrong was not explicitly defining my CASE so that I could explicitly end that case?

If you have time, can you please explain the statement that I'm far, FAR better off using related tables?  I'm a NOOB in every sense of the word!

Thanks!
0
Scott MilnerApplication AdministratorAuthor Commented:
when I tried your statement, I received

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'ST'.
0
Scott MilnerApplication AdministratorAuthor Commented:
never mind the syntax error comment... forgot the comma after END AS SALESSTATUS,
0
Scott MilnerApplication AdministratorAuthor Commented:
I found the information on related tables.

Thanks for the help with this query!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.