This is experimental functionality, and as such subject to change. Use at your own risk!
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!