Oracle SQL - CASE syntax

Question

Assume this query which functions fine:

SELECT 
   first_name,
   consultant_id,
   CASE consultant_id
    WHEN 1 THEN 'First Consultant'
    WHEN 2 THEN 'Second Consultant'
   END
FROM consultant
ORDER BY first_name;

Why couldn't I add another WHEN clause using some Boolean logic other than the implied equal to in the two WHEN clauses above? For example:

SELECT 
   first_name,
   consultant_id,
   CASE consultant_id
    WHEN 1 THEN 'First Consultant'
    WHEN 2 THEN 'Second Consultant'
    WHEN BETWEEN 3 AND 12 THEN 'Everyone else'
   END
FROM consultant
ORDER BY first_name;

Which throws this error:

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 7 Column: 10

Is there a way to make this work without using the more verbose:

SELECT 
   first_name,
   consultant_id,
   CASE 
    WHEN consultant_id = 1 THEN 'First Consultant'
    WHEN consultant_id = 2 THEN 'Second Consultant'
    WHEN consultant_id BETWEEN 3 AND 12 THEN 'Everyone else'
   END
FROM consultant
ORDER BY first_name;

You now the answer. The "more verbose" method is what you need to use. — Gordon Linoff, Jun 05 '18 at 13:19
So the WHEN clause can only handle the implied 'equal to' logic? — Conner M., Jun 05 '18 at 13:23
@ConnerM.: correct, if you need anything else you can't use the "short version" — , Jun 05 '18 at 13:31

score 2 · Accepted Answer · answered Jun 05 '18 at 13:50

2

Yes, you are right, it is unfortunately not possible to have conditions in the short syntax.

The documentation calls the short syntax "simple_case_expression"

CASE expr WHEN comparision_expr THEN return_expr

answered Jun 05 '18 at 13:50

wolφi

8,091
2
35
64

score 2 · Answer 2 · edited Jul 01 '20 at 17:56

2

Also, depending on your requirement, you may be able to use:

SELECT
   first_name,
   consultant_id,
   CASE consultant_id
    WHEN 1 THEN 'First Consultant'
    WHEN 2 THEN 'Second Consultant'
    ELSE 'Everyone else'
   END
FROM consultant
ORDER BY first_name
/

edited Jul 01 '20 at 17:56

halfer

19,824
17
99
186

answered Jun 05 '18 at 13:55

Mark J. Bobak

13,720
6
39
67

Oracle SQL - CASE syntax

2 Answers2