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 must always come 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
GROUP BY Class