New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3

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.

The jOOQ Logo