B.2.2 Obtaining Comparison Result from more than 50 Conditional Expressions

Oracle database

SELECT DECODE(col1,
                1,'A',
                2,'B',
                ...
                78,'BZ',
                NULL,'UNKNOWN',
                'OTHER')
    FROM t1;

* col1 is assumed to be INTEGER type

Symfoware Server

SELECT CASE 
           WHEN col1 = 1 THEN 'A'
           WHEN col1 = 2 THEN 'B'
           ...
           WHEN col1 = 78 THEN 'BZ'
           WHEN col1 IS NULL THEN 'UNKNOWN'
           ELSE 'OTHER'
         END
    FROM t1;

* col1 is assumed to be INTEGER type

Feature differences

Oracle database: Search value with a maximum of 127 items (up to 255 arguments in total) can be specified.

Symfoware Server: Search value with a maximum of 49 items (up to 100 arguments in total) only can be specified.

Conversion procedure

Convert to the CASE expression using the following procedure:

Specify the DECODE conversion target value expression (col1 in the first argument, in the example) and the search value (1 in the second argument, in the example) for the CASE expression search condition. Specify the DECODE result value ('A' in the third argument, in the example) for the CASE expression THEN (WHEN col1 = 1 THEN 'A', in the example). Note that if the search value is NULL, specify "IS NULL" for the search condition for the CASE expression.
If the DECODE default value ('OTHER' in the last argument, in the example) is specified, specify the default value for the CASE expression ELSE (ELSE 'OTHER', in the example).