B.2.1 Comparing Numeric Data of Character String Types and Numeric Characters

Oracle database

SELECT DECODE( col1,
               1000, 'ITEM-A',
               2000, 'ITEM-B',
               'ITEM-C')
  FROM t1;

Note: col1 is assumed to be CHAR(4) type

Fujitsu Enterprise Postgres

SELECT DECODE( CAST(col1 AS INTEGER),
               1000, 'ITEM-A',
               2000, 'ITEM-B',
               'ITEM-C')
  FROM t1;

Note: col1 is assumed to be CHAR(4) type

Feature differences

Oracle database: When the value expression is a string and the search value is a numeric, the string value will be converted to the data type of the comparison target numeric, so that they can be compared.

Fujitsu Enterprise Postgres: If the conversion target value expression is a string value, then no search value can be specified with numbers.

Conversion procedure: Since the data type that can be specified for the conversion target value expression is unknown, use CAST to explicitly convert the conversion target value expression (col1 in the example) to a numeric (INTEGER type in the example).