ORACLE SQL CASE statement - getting error

0
votes
SELECT
    ZOC, UNIT,
    CASE ZOC
        WHEN ZOC = '51' THEN 'ZONE OPERATION KHULNA'
        WHEN ZOC = '52' THEN 'ZONE OPERATION JESSORE'
        WHEN XOC = '53' THEN 'ZONE OPERATION KUSHTIA'
    END
FROM 
    GNGRB.BS_CLOSING
ORDER BY 
    ZOC;

I get this error:

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 16 Column: 10

3
sqloraclesyntax-error
On a side note: Is zoc a string column or why are you using quotes ('51' etc.)? If it is a numeric column, you should use numbers, e.g. WHEN ZOC = 51. – Thorsten Kettner
The best solution would of course be a zones table mapping the zone strings to the zoc numbers, but I guess you know that. – Thorsten Kettner

3 Answers

2
votes

You are mixing two syntaxes. Decide for one.

CASE zoc WHEN ...

SELECT 
  zoc, unit,
  CASE zoc
    WHEN 51 THEN 'ZONE OPERATION KHULNA'
    WHEN 52 THEN 'ZONE OPERATION JESSORE'
    WHEN 53 THEN 'ZONE OPERATION KUSHTIA'
  END 
FROM ...

CASE WHEN ...

SELECT 
  zoc, unit,
  CASE
    WHEN zoc = 51 THEN 'ZONE OPERATION KHULNA'
    WHEN zoc = 52 THEN 'ZONE OPERATION JESSORE'
    WHEN zoc = 53 THEN 'ZONE OPERATION KUSHTIA'
  END 
FROM ...
1
votes
SELECT ZOC, UNIT, CASE WHEN ZOC = '51' THEN 'ZONE OPERATION KHULNA'
                       WHEN ZOC = '52' THEN 'ZONE OPERATION JESSORE'
                       WHEN ZOC = '53' THEN 'ZONE OPERATION KUSHTIA'
                  END 
FROM GNGRB.BS_CLOSING 
ORDER BY ZOC;
0
votes

There is two way of writing the CASE..WHEN statement.

  1. CASE WHEN COLUMN_NAME = VALUE1 THEN ... WHEN COLUMN_NAME = VALUE2 THEN .. END
  2. CASE COLUMN_NAME WHEN VALUE1 THEN ... WHEN VALUE2 THEN ... END

In your case, You can write it as follows:

SELECT ZOC,
       UNIT,
       CASE 
       --ZOC -- just remove this
           WHEN ZOC = '51' THEN
               'ZONE OPERATION KHULNA'
           WHEN ZOC = '52' THEN
               'ZONE OPERATION JESSORE'
           WHEN ZOC = '53' THEN
               'ZONE OPERATION KUSHTIA'
       END
  FROM GNGRB.BS_CLOSING
 ORDER BY ZOC;

-- OR

SELECT ZOC,
       UNIT,
       CASE ZOC
           WHEN '51'   THEN
               'ZONE OPERATION KHULNA'
           WHEN '52'   THEN
               'ZONE OPERATION JESSORE'
           WHEN '53'   THEN
               'ZONE OPERATION KUSHTIA'
       END
  FROM GNGRB.BS_CLOSING
 ORDER BY ZOC;