Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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
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, MySQL
CASE WHEN (AUTHOR.FIRST_NAME <=> 'Paulo') THEN 'BR' WHEN (AUTHOR.FIRST_NAME <=> 'George') THEN 'EN' ELSE 'unknown' END
Aurora Postgres, BigQuery, CockroachDB, DuckDB, Firebird, HSQLDB, Postgres, SQLServer, Snowflake, Trino, 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
ClickHouse
CASE WHEN arrayUniq(ARRAY(AUTHOR.FIRST_NAME, 'Paulo')) = 1 THEN 'BR' WHEN arrayUniq(ARRAY(AUTHOR.FIRST_NAME, 'George')) = 1 THEN 'EN' ELSE 'unknown' END
DB2, Databricks, Exasol, H2, Informix, MemSQL, Oracle, Teradata, Vertica
decode( AUTHOR.FIRST_NAME, 'Paulo', 'BR', 'George', 'EN', 'unknown' )
Derby
CASE
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM SYSIBM.SYSDUMMY1
INTERSECT
SELECT 'Paulo' x
FROM SYSIBM.SYSDUMMY1
) THEN 'BR'
WHEN EXISTS (
SELECT AUTHOR.FIRST_NAME x
FROM SYSIBM.SYSDUMMY1
INTERSECT
SELECT 'George' x
FROM SYSIBM.SYSDUMMY1
) THEN 'EN'
ELSE 'unknown'
END
Hana
map( AUTHOR.FIRST_NAME, 'Paulo', 'BR', 'George', 'EN', 'unknown' )
MariaDB
decode_oracle( AUTHOR.FIRST_NAME, 'Paulo', 'BR', 'George', 'EN', 'unknown' )
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 */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!