Available in versions: Dev (3.20) | Latest (3.19) | 3.18

Simplify CASE abbreviations

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The NVL or COALESCE CASE abbreviations may appear in expressions that have more simple counterparts using a simpler CASE abbreviation.

Using Settings.transformPatternsSimplifyCaseAbbreviation, the following transformations can be achieved:

-- With Settings.transformPatternsSimplifyCaseAbbreviation active, this:
SELECT
  a IS NULL OR NVL(a = b, FALSE),
  COALESCE(a = b, a IS NULL),
  COALESCE(a, b) IS NOT DISTINCT FROM b,
  a IS NOT NULL AND NVL(a != b, TRUE),
  COALESCE(a != b, a IS NOT NULL),
  COALESCE(a, b) IS DISTINCT FROM b
FROM tab;

-- ... is transformed into the equivalent expression:
SELECT
  NULLIF(a, b) IS NULL,
  NULLIF(a, b) IS NULL,
  NULLIF(a, b) IS NULL,
  NULLIF(a, b) IS NOT NULL,
  NULLIF(a, b) IS NOT NULL,
  NULLIF(a, b) IS NOT NULL
FROM tab;

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo