CASE

The SQL standard does not allow the use of non-deterministic functions, like the NPS® random() function, in CASE expression WHEN clauses. But, as an extension to the standard, NPS SQL allows such usage. Use caution with this type of expression, as the behavior might not be what is expected.

Example:
SELECT CASE WHEN random() = .1 THEN 'A' WHEN random() = .2 THEN 'B'
ELSE 'C' END FROM tblA
The system evaluates the expression in the following manner:
  • Generate a random number
  • If the generated number is .1, then return 'A'
  • Generate a second random number
  • If the newly generated number is .2, then return 'B'
  • Otherwise, return 'C'

So the system evaluates the random() function separately when it evaluates each WHEN expression. This is important when non-deterministic functions like random() are involved, and each execution can return a different value.

If you wanted a different behavior in the previous example, you can use a subquery, as in the following example:
SELECT CASE WHEN rand = .1 THEN 'A' WHEN rand = .2 THEN 'B' ELSE 'C'
END
FROM (SELECT random() rand FROM tblA LIMIT ALL) subset

The LIMIT ALL in the subquery prevents it from being pulled up into the parent query, and the random() function is invoked only once for each row of tblA, and therefore the same random() result is tested in each WHEN clause.

The previous example used a form of CASE expression that the standard calls a searched CASE. But SQL offers another form of CASE expression, called simple CASE.

Original example in simple CASE form:
SELECT CASE random() WHEN .1 THEN 'A' WHEN .2 THEN 'B' ELSE 'C' END
FROM tblA

In this form, it looks like the random() function is invoked once. But the standard says that the simple CASE expression means exactly the same thing as the more verbose searched CASE equivalent. So the system handles both of these examples in the same way, and the same cautions apply.

There are other CASE variants like NULLIF, NVL, NVL2, COALESCE, and DECODE that are converted to CASE expressions, and so show the same behavior and call for the same caution.