[DERBY-1576] Extend the CASE expression syntax for "simple case"

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Low
• Assignee: Knut Anders Hatlen
• Reporter: Christian d'Heureuse
• Created: 23/Jul/06 13:02
• Updated: 09/Sep/16 09:54
• Resolved: 02/Jun/14 07:59

Description

The ISO/IEC 9075-2:1999 SQL standard describes two kinds of CASE expressions: "simple case" and "searched case".
The current Derby version supports "searched case" but not "simple case".

The syntax for "simple case" is:

CASE Expression
WHEN Expression THEN Expression
[ WHEN Expression THEN Expression ]
...
ELSE ElseExpression
END

Example:

VALUES
CASE 4
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
ELSE 'many'
END

Issue Links

• incorporates: DERBY-6581 Document simple case syntax
• is related to: DERBY-6569 NULLIF may return incorrect results if first operand calls non-deterministic function