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

This is experimental functionality, and as such subject to change. Use at your own risk!

# 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 