Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8

DECODE

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Some SQL dialects, including Db2, H2, Oracle know a more succinct, but maybe less readable DECODE() function with a variable number of arguments. This function works like a NULL safe CASE expression. jOOQ supports the DECODE() function and emulates it using CASE expressions in all dialects that do not have native support:

SELECT
  -- Oracle:
  DECODE(FIRST_NAME, 'Paulo', 'brazilian',
                     'George', 'english',
                     'unknown'),
  -- Other SQL dialects
  CASE 
    WHEN FIRST_NAME IS NOT DISTINCT FROM 'Paulo'  THEN 'brazilian'
    WHEN FIRST_NAME IS NOT DISTINCT FROM 'George' THEN 'english'
    ELSE 'unknown'
  END
FROM AUTHOR




// Use the Oracle-style DECODE() function with jOOQ.
// Note, that you will not be able to rely on type-safety
decode(
  AUTHOR.FIRST_NAME,
  "Paulo", "brazilian",
  "George", "english",
  "unknown"
);

See the DISTINCT predicate for details about the NULL safe semantics.

Dialect support

This example using jOOQ:

decode(AUTHOR.FIRST_NAME, "Paulo", "BR", "George", "EN", "unknown")

Translates to the following dialect specific expressions:

-- ASE, SQLDATAWAREHOUSE, SQLSERVER
CASE
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME x
    INTERSECT
    SELECT 'Paulo' x
  ) THEN 'BR'
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME x
    INTERSECT
    SELECT 'George' x
  ) THEN 'EN'
  ELSE 'unknown'
END

-- AURORA_MYSQL, MARIADB, MEMSQL, MYSQL
CASE
  WHEN (AUTHOR.FIRST_NAME <=> 'Paulo') THEN 'BR'
  WHEN (AUTHOR.FIRST_NAME <=> 'George') THEN 'EN'
  ELSE 'unknown'
END

-- AURORA_POSTGRES, BIGQUERY, COCKROACHDB, FIREBIRD, HSQLDB, POSTGRES, SNOWFLAKE, YUGABYTEDB
CASE
  WHEN AUTHOR.FIRST_NAME IS NOT DISTINCT FROM 'Paulo' THEN 'BR'
  WHEN AUTHOR.FIRST_NAME IS NOT DISTINCT FROM 'George' THEN 'EN'
  ELSE 'unknown'
END

-- DB2, EXASOL, H2, ORACLE, TERADATA, VERTICA
decode(
  AUTHOR.FIRST_NAME,
  'Paulo',
  'BR',
  'George',
  'EN',
  'unknown'
)

-- DERBY
CASE
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME AS x
    FROM SYSIBM.SYSDUMMY1
    INTERSECT
    SELECT 'Paulo' AS x
    FROM SYSIBM.SYSDUMMY1
  ) THEN 'BR'
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME AS x
    FROM SYSIBM.SYSDUMMY1
    INTERSECT
    SELECT 'George' AS x
    FROM SYSIBM.SYSDUMMY1
  ) THEN 'EN'
  ELSE 'unknown'
END

-- HANA
map(
  AUTHOR.FIRST_NAME,
  'Paulo',
  'BR',
  'George',
  'EN',
  'unknown'
)

-- INFORMIX
CASE
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME x
    FROM (
      SELECT 1 AS dual
      FROM systables
      WHERE (tabid = 1)
    ) AS dual
    INTERSECT
    SELECT 'Paulo' x
    FROM (
      SELECT 1 AS dual
      FROM systables
      WHERE (tabid = 1)
    ) AS dual
  ) THEN 'BR'
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME x
    FROM (
      SELECT 1 AS dual
      FROM systables
      WHERE (tabid = 1)
    ) AS dual
    INTERSECT
    SELECT 'George' x
    FROM (
      SELECT 1 AS dual
      FROM systables
      WHERE (tabid = 1)
    ) AS dual
  ) THEN 'EN'
  ELSE 'unknown'
END

-- REDSHIFT
CASE
  WHEN NOT (AUTHOR.FIRST_NAME IS DISTINCT FROM 'Paulo') THEN 'BR'
  WHEN NOT (AUTHOR.FIRST_NAME IS DISTINCT FROM 'George') THEN 'EN'
  ELSE 'unknown'
END

-- SQLITE
CASE
  WHEN (AUTHOR.FIRST_NAME IS 'Paulo') THEN 'BR'
  WHEN (AUTHOR.FIRST_NAME IS 'George') THEN 'EN'
  ELSE 'unknown'
END

-- SYBASE
CASE
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME x
    FROM SYS.DUMMY
    INTERSECT
    SELECT 'Paulo' x
    FROM SYS.DUMMY
  ) THEN 'BR'
  WHEN EXISTS (
    SELECT AUTHOR.FIRST_NAME x
    FROM SYS.DUMMY
    INTERSECT
    SELECT 'George' x
    FROM SYS.DUMMY
  ) THEN 'EN'
  ELSE 'unknown'
END

-- ACCESS
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.18, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo