HSQLDB script file formatting

Question

Please excuse this neophytic question This script file works for me under HSQL 2.7.2:

SELECT
   CASE col1 WHEN "1" THEN "one" ELSE "NOT" END
FROM tab;

This does not work:

SELECT
   CASE col1
      WHEN "1" THEN "one"
      ELSE "NOT"
      END
   FROM tab;

Have I failed to set a parameter or something else? Thanks for the patience.

Tried various SQL CASE formats with no luck.

Answer 1

You reported the same SELECT statement as working and not working.

If col1 is defined as INTEGER or other numeric type, do not use quotes for the WHEN value. The literal strings should be single quoted rather than double quoted. For example:

SELECT CASE col1 WHEN 1 THEN 'one' ELSE 'NOT' END FROM tab;

Will return a list such as this:

one
NOT
one