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
CHOOSE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The CHOOSE()
function acts as a switch over an integer to return the nth argument. It is an abbreviated CASE expression
SELECT choose(1, 'a', 'b'), choose(2, 'a', 'b'), choose(3, 'a', 'b');
create.select( choose(val(1), val("a"), val("b")), choose(val(2), val("a"), val("b")), choose(val(3), val("a"), val("b"))).fetch();
The result being
+--------+--------+--------+ | choose | choose | choose | +--------+--------+--------+ | a | b | {null} | +--------+--------+--------+
Dialect support
This example using jOOQ:
choose(val(1), val("a"), val("b"))
Translates to the following dialect specific expressions:
Access
SWITCH(1 = 1, 'a', 1 = 2, 'b')
ASE, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
CASE 1 WHEN 1 THEN 'a' WHEN 2 THEN 'b' END
Aurora MySQL, MariaDB, MemSQL, MySQL
elt(1, 'a', 'b')
Derby
CASE WHEN 1 = 1 THEN 'a' WHEN 1 = 2 THEN 'b' END
SQLServer
choose(1, 'a', 'b')
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!