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, COCKROACHDB, DB2, DUCKDB, EXASOL, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, ORACLE, -- POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, 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.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!