March 25, 2010 at 10:40 am
I am having a problem with the first case statement in my query. Its telling me that the error is Line 2: Incorrect syntax near '='. This is the query. If I take that case statement out the query runs without error. Can someone please help me.
Many Thanks,
selectA.SEQUENCE AS "WORK ORDER",
A.[SEQ_SEVERITY:] = CASE
WHEN A.[SEQ_SEVERITY:] = 1 THEN 'HIGH'
WHEN A.[SEQ_SEVERITY:] = 3 THEN 'MEDIUM'
WHEN A.[SEQ_SEVERITY:] = 4 THEN 'LOW' WHEN A.[SEQ_SEVERITY:] = 1002 THEN 'CRITICAL'
End,
CASE WHEN A.HDNUM IS NULL
THEN '' ELSE A.HDNUM END AS "INCIDENT",
G.NAME AS "OPENED BY",
CASE WHEN H.NAME IS NULL
THEN '' ELSE H.NAME END AS "CLOSED BY",
CASE WHEN E.DESCRIPTION IS NULL
THEN '' ELSE E.DESCRIPTION END AS "PARENT_DESC",
B.CODE AS "CATEGORY ID",
B.DESCRIPTION AS "CATEGORY DESCRIPTION",
A.[DATE OPEN] AS DATE_OPENED,
CASE WHEN D.NAME IS NULL
THEN '' ELSE D.NAME END AS "PERSON ASSIGNED TO",
F.CODE AS "GROUP ASSIGNED TO",
CASE WHEN A.[CLOSED ON] IS NULL
THEN '' ELSE A.[CLOSED ON] END AS DATE_CLOSED,
A.DESCRIPTION,
CASE WHEN A.RESOLUTION IS NULL
THEN '' ELSE A.RESOLUTION END AS "RESOLUTION",
C.FNAME + ' ' + C.NAME AS CLIENT_LNAME,
A.STATUS AS "WO STATUS",
I.STATUS AS "INCIDENT STATUS",
A.SAP_STAGE,
A.[SEQ_SEVERITY:] AS "URGENCY"
FROM_SMDBA_._WORKORD_ A
LEFT JOIN _SMDBA_._SUBJECTS_ B ON A.SUBJECT = B.SEQUENCE
LEFT JOIN _SMDBA_._SUBJECTS_ E ON B.PARENT = E.SEQUENCE
LEFT JOIN _SMDBA_._CUSTOMER_ C ON A.CLIENT = C.SEQUENCE
LEFT JOIN _SMDBA_._PERSONNEL_ D ON A.[SENT TO] = D.SEQUENCE
LEFT JOIN _SMDBA_._GROUPS_ F ON A._GROUP_ = F.SEQUENCE
LEFT JOIN _SMDBA_._PERSONNEL_ G ON A.[OPEN BY] = G.SEQUENCE
LEFT JOIN _SMDBA_._PERSONNEL_ H ON A.[CLOSED BY] = H.SEQUENCE
LEFT JOIN _SMDBA_._TELMASTE_ I ON A.HDNUM = I.SEQUENCE
where A._GROUP_ IN (
'1069',
'1065',
'1064',
'1063',
'1058',
'1075' )
March 25, 2010 at 10:56 am
You could use one of the following options. The reason your solution didn't work is you've assigned a table column instead of an alias name.
Side note: I added an ELSE clause to the options below...
select A.SEQUENCE AS "WORK ORDER",
[SEQ_SEVERITY:] = CASE
WHEN A.[SEQ_SEVERITY:] = 1 THEN 'HIGH'
WHEN A.[SEQ_SEVERITY:] = 3 THEN 'MEDIUM'
WHEN A.[SEQ_SEVERITY:] = 4 THEN 'LOW' WHEN A.[SEQ_SEVERITY:] = 1002 THEN 'CRITICAL'
ELSE 'UNKNOWN'
End ,
-- or
select A.SEQUENCE AS "WORK ORDER",
[SEQ_SEVERITY:] = CASE A.[SEQ_SEVERITY:]
WHEN 1 THEN 'HIGH'
WHEN 3 THEN 'MEDIUM'
WHEN 4 THEN 'LOW'
WHEN 1002 THEN 'CRITICAL'
ELSE 'UNKNOWN'
End AS [SEQ_SEVERITY:],
March 25, 2010 at 11:26 am
Thank you very much. I now see what I did wrong.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply