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
3 = Activated Pick
4 = Pick Started
10 = Pick Complete
20 = Pick Canceled
My actual SQL query is as follows:
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'
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.