This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
CASE to CASE abbreviation
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some case CASE expressions can be turned into COALESCE, NULLIF, NVL2 or other "case abbreviations".
Using Settings.transformPatternsCaseToCaseAbbreviation, the following transformations can be achieved:
-- With Settings.transformPatternsCaseToCaseAbbreviation active, this: SELECT CASE WHEN x IS NULL THEN y ELSE x END, CASE WHEN x = y THEN NULL ELSE x END, CASE WHEN x IS NOT NULL THEN y ELSE z END, CASE WHEN x IS NULL THEN y ELSE z END, CASE WHEN x = 1 THEN y WHEN x = 2 THEN z END, FROM tab; -- ... is transformed into the equivalent expression: SELECT NVL(x, y), NULLIF(x, y), NVL2(x, y, z), NVL2(x, z, y), CHOOSE(x, y, z) FROM tab;
Some additional special cases appear with BOOLEAN types involved:
-- With Settings.transformPatternsCaseToCaseAbbreviation active, this: SELECT CASE WHEN x = y THEN TRUE ELSE FALSE END, CASE WHEN x = y THEN FALSE ELSE TRUE END, CASE x WHEN y THEN TRUE ELSE FALSE END, CASE x WHEN y THEN FALSE ELSE TRUE END FROM tab; -- ... is transformed into the equivalent expression: SELECT NVL(x = y, FALSE), NVL(x <> y, TRUE), NVL(x = y, FALSE), NVL(x <> y, TRUE) FROM tab;
Feedback
Do you have any feedback about this page? We'd love to hear it!