Trivial form of CASE is not supported in DERBY Parser

[Ori Herrnstadt]

The following query syntax fails:

SELECT c,
       d-e,
       CASE a+1 WHEN b THEN 111 WHEN c THEN 222
        WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END,
       a+b*2+c*3+d*4,
       e
  FROM t1
 WHERE d NOT BETWEEN 110 AND 150
    OR c BETWEEN b-2 AND d+2
    OR (e>c OR e<d)
 ORDER BY 1,5,3,2,4

With the following error:

ERROR: Encountered " <IDENTIFIER> "a "" at line 3, column 13.
Was expecting:
    "when" ...

LINE 3: CASE a+1 WHEN b THEN 111 WHEN c THEN 222
                    ^

When the CASE comparison expression is re-written as 'a+1 = x' it passes:

SELECT c,
       d-e,
       CASE WHEN a+1 = b THEN 111 WHEN a+1 = c THEN 222
        WHEN a+1 = d THEN 333 WHEN a+1 = e THEN 444 ELSE 555 END,
       a+b*2+c*3+d*4,
       e
  FROM t1
 WHERE d NOT BETWEEN 110 AND 150
    OR c BETWEEN b-2 AND d+2
    OR (e>c OR e<d)
 ORDER BY 1,5,3,2,4

See also Derby thread from 2011: http://apache-database.10148.n7.nabble.com/CASE-expression-WHEN-expression-THEN-expression-END-syntax-td101242.html

[Mike McMahon (mmcm)]

Or http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj151.html. The simple form is not supported, only the searched form.