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