Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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, 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.20. Translate your own SQL on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!