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
– Gordon Linoff, Commented Jun 5, 2018 at 13:19
So the WHEN clause can only handle the implied 'equal to' logic? — Conner M.
– Conner M., Commented Jun 5, 2018 at 13:23
@ConnerM.: correct, if you need anything else you can't use the "short version" — user330315
– user330315, Commented Jun 5, 2018 at 13:31

wolφi · Accepted Answer · 2018-06-05 13:50:02Z

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 5, 2018 at 13:50

wolφi

8,3814 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

halfer · Accepted Answer · 2020-07-01 17:56:09Z

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 1, 2020 at 17:56

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered Jun 5, 2018 at 13:55

Mark J. Bobak

14.5k6 gold badges45 silver badges73 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - CASE syntax

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related