ORA-00905 MISSING KEYWORD in case statement..though the syntax is correct

Question

I'm getting the missing keyword error ORA-00905 for the query

SELECT '01_AMT' AS FIELD, COUNT((NVL(AMT,0)) AS CNT FROM  TBL1

UNION ALL

SELECT 

CASE 
WHEN  AMT IS NULL THEN 'NULL'

WHEN AMT<10000 THEN '<10000'

WHEN AMT>= 10000 and AMT<=59999 THEN '10,000-59,999’

ELSE '60,000up' END,


COUNT (1) CNT FROM TBL1

GROUP BY CASE

WHEN  AMT IS NULL THEN 'NULL'

WHEN AMT<10000 THEN '<10000'

WHEN AMT>= 10000 and AMT<=59999 THEN '10,000-59,999’

ELSE '60,000up' END

output:

FIELD CNT

01_Amt 100000

10,000-59,999 50000

60,000up 50000

The first field '01-Amt' should have the total count and breakdown of the other counts from the second row. Amt is number datatype.

This should help: http://stackoverflow.com/questions/18104884/conditional-where-clause-with-case-statement-in-oracle — Kris Gruttemeyer, Jul 18 '14 at 21:00
My issue is resolved with the changed tick..Could u pls tell me where it is located on the keyboard..I used the one next to 'enter'button..and couldn't find similar one on the keyboard.. — Preddy, Jul 21 '14 at 14:25
Microsoft Word automatically changes the normal tick to that curled one. If you typed part of this in word, outlook, or received from someone else than it should have been changed. The curled tick does not exist in most normal keyboards. — kurast, Jul 21 '14 at 14:48

score 0 · Accepted Answer · answered Jul 18 '14 at 21:17

You are not using the standard closing tick, but that curly one. It is not understood by Oracle, and it ignores the rest of the sentence. Correction:

SELECT '01_AMT' AS FIELD, COUNT((NVL(AMT,0)) AS CNT FROM  TBL1

UNION ALL

SELECT 

CASE 
WHEN  AMT IS NULL THEN 'NULL'

WHEN AMT<10000 THEN '<10000'

WHEN AMT>= 10000 and AMT<=59999 THEN '10,000-59,999' <= CHANGED THIS TICK!

ELSE '60,000up' END,


COUNT (1) CNT FROM TBL1

GROUP BY CASE

WHEN  AMT IS NULL THEN 'NULL'

WHEN AMT<10000 THEN '<10000'

WHEN AMT>= 10000 and AMT<=59999 THEN '10,000-59,999' <= CHANGED THIS TICK!

ELSE '60,000up' END

ORA-00905 MISSING KEYWORD in case statement..though the syntax is correct

1 Answers1