sqloracle-databasecase-whenbooleanquery

Oracle SQL - CASE syntax


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;

Solution

  • 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