CODEWITHSUNDEEP
sqloracle-databasesyntax-error
ownmurad
ownmurad

Reputation: 11

ORACLE SQL CASE statement - getting error

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

Upvotes: 0

Views: 286

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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 ...

Upvotes: 2

Popeye
Popeye

Reputation: 35900

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;

Upvotes: 0

juergen d
juergen d

Reputation: 204756

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;

Upvotes: 1

Related Questions