CASE

El estándar SQL no permite el uso de funciones no deterministas, como la función NPS® ' random(), en las cláusulas WHEN de las expresiones CASE. Pero, como extensión de la norma, NPS SQL permite ese uso. Tenga cuidado con este tipo de expresión, ya que el comportamiento puede que no sea el esperado.

Ejemplo:
SELECT CASE WHEN random() = .1 THEN 'A' WHEN random() = .2 THEN 'B'
ELSE 'C' END FROM tblA
El sistema evalúa la expresión de la siguiente manera:
  • Genera un número aleatorio
  • Si el número generado es .1, entonces devuelve 'A'
  • Genera un segundo número aleatorio
  • Si el número recién generado es .2, entonces devuelve 'B'
  • De lo contrario, devuelve 'C'

Así que el sistema evalúa la función random() de forma separada cuando evalúa cada una de las expresiones WHEN. Es importante cuando están implicadas funciones no deterministas como random(), y cada ejecución puede devolver un valor diferente.

Si deseaba un comportamiento diferente en el ejemplo anterior, puede utilizar una subconsulta como en el ejemplo siguiente:
SELECT CASE WHEN rand = .1 THEN 'A' WHEN rand = .2 THEN 'B' ELSE 'C'
END
FROM (SELECT random() rand FROM tblA LIMIT ALL) subset

LIMIT ALL en la consulta impide que llegue a la consulta padre y que se invoque la función random() solo una vez para cada fila de tblA y, por lo tanto, se pruebe el mismo resultado random() en cada cláusula WHEN.

El ejemplo anterior ha utiliza un formato de expresión CASE que el estándar llama un CASE buscado. Pero SQL ofrece otro formato de expresión CASE, llamada CASE simple.

Ejemplo original en formato CASE simple:
SELECT CASE random() WHEN .1 THEN 'A' WHEN .2 THEN 'B' ELSE 'C' END
FROM tblA

En este formato, parece que la función random() se invoca una vez. Pero el estándar dice que la expresión CASE simple significa exactamente los mismo que el equivalente CASE buscado más detallado. Así que el sistema maneja estos ejemplos de la misma forma, y se aplica el mismo cuidado.

Hay otras variantes CASE como NULLIF, NVL, NVL2, COALESCE y DECODE que se convierten en expresiones CASE y, por tanto, muestran el mismo comportamiento y piden el mismo cuidado.