CASE

CASE expressions allow conditional processing in EQL, allowing you to make decisions at query time.

The syntax of the CASE expression, which conforms to the SQL standard, is:

CASE
     WHEN <Boolean-expression> THEN <expression>
     [WHEN <Boolean-expression> THEN <expression>]*
     [ELSE expression]
END

CASE expressions must include at least one WHEN expression. The first WHEN expression with a TRUE condition is the one selected. NULL is not TRUE. The optional ELSE clause, if it appears, must appear at the end of the CASE statement and is equivalent to WHEN TRUE THEN. If no condition matches, the result is NULL or the empty set, depending on the data type of the THEN expressions.

In this example, division by non-positive integers is avoided:

CASE
     WHEN y < 0 THEN x / (0 - y)
     WHEN y > 0 THEN x / y
     ELSE 0
END

In this example, records are categorized as Recent or Old:

RETURN Result AS
SELECT
  CASE
    WHEN (Days < 7) THEN 'Recent'
    ELSE ‘Old’
  END AS Age
...

The following example groups all records by class and computes the following:

The minimum DealerPrice of all records in class H.
The minimum ListPrice of all records in class M.
The minimum StandardCost of all other records (called class L).

RETURN CaseExample AS 
SELECT
   CASE
     WHEN Class = 'H' THEN MIN(DealerPrice)
     WHEN Class = 'M' THEN MIN(ListPrice)
     ELSE MIN(StandardCost)
   END
AS value
FROM SaleState
GROUP BY Class