Funciones generales de SQL | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL y NANVL

En este artículo, analizaremos algunas potentes funciones generales de SQL, que son: NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL y NANVL.

Estas funciones funcionan con cualquier tipo de datos y pertenecen al uso de valores nulos en la lista de expresiones. Todas estas son funciones de una sola fila, es decir, proporcionan un resultado por fila.

• NVL(expr1, expr2) : En SQL, NVL() convierte un valor nulo en un valor real. Los tipos de datos que se pueden utilizar son fecha, carácter y número. El tipo de datos debe coincidir entre sí, es decir, expr1 y expr2 deben ser del mismo tipo de datos.
  Sintaxis –

  NVL (expr1, expr2)
  

  expr1 es el valor de origen o la expresión que puede contener un valor nulo.
  expr2 es el valor objetivo para convertir el nulo.

  Ejemplo –

  SELECT  salary, NVL(commission_pct, 0),
      (salary*12) + (salary*12*NVL(commission_pct, 0))
        annual_salary FROM employees;
  

  Producción :
  

• NVL2(expr1, expr2, expr3) : La función NVL2 examina la primera expresión. Si la primera expresión no es nula, la función NVL2 devuelve la segunda expresión. Si la primera expresión es nula, se devuelve la tercera expresión, es decir, si expr1 no es nula, NVL2 devuelve expr2. Si expr1 es nulo, NVL2 devuelve expr3. El argumento expr1 puede tener cualquier tipo de datos.

  Sintaxis –

  NVL2 (expr1, expr2, expr3)
  

  expr1 es el valor de origen o la expresión que puede contener un valor nulo
  expr2 es el valor devuelto si expr1 no es nulo
  expr3 es el valor devuelto si expr1 es nulo

  Ejemplo –

  SELECT last_name, salary, commission_pct,
   NVL2(commission_pct, ’SAL+COMM’, ’SAL’)
   income FROM employees;
  

  Producción :
  

• DECODE() : Facilita las consultas condicionales haciendo el trabajo de una sentencia CASE o IF-THEN-ELSE.
  La función DECODE decodifica una expresión de forma similar a la lógica IF-THEN-ELSE utilizada en varios idiomas. La función DECODE decodifica la expresión después de compararla con cada valor de búsqueda. Si la expresión es la misma que la búsqueda, se devuelve el resultado.
  Si se omite el valor predeterminado, se devuelve un valor nulo donde un valor de búsqueda no coincide con ninguno de los valores de resultado.

  Sintaxis –

  DECODE(col|expression, search1, result1 
   [, search2, result2,...,][, default])
  

  Ejemplo –

  SELECT last_name, job_id, salary,
     DECODE(job_id, ’IT_PROG’, 1.10*salary,
      ’ST_CLERK’, 1.15*salary,
      ’SA_REP’, 1.20*salary,salary) 
       REVISED_SALARY FROM employees;
  

  Producción :
  

• COALESCE() : La función COALESCE() examina la primera expresión, si la primera expresión no es nula, devuelve esa expresión; De lo contrario, hace un COALESCE de las expresiones restantes.
  La ventaja de la función COALESCE() sobre la función NVL() es que la función COALESCE puede tomar múltiples valores alternativos. En palabras simples, la función COALESCE() devuelve la primera expresión no nula de la lista.

  Sintaxis –

  COALESCE (expr_1, expr_2, ... expr_n)
  

  Ejemplos –

  SELECT last_name, 
      COALESCE(commission_pct, salary, 10) comm
      FROM employees ORDER BY commission_pct;
  

  Producción :
  

• NULLIF() : La función NULLIF compara dos expresiones. Si son iguales, la función devuelve nulo. Si no son iguales, la función devuelve la primera expresión. No puede especificar el NULL literal para la primera expresión.

  Sintaxis –

  NULLIF (expr_1, expr_2)
  

  Ejemplos –

  SELECT LENGTH(first_name) "expr1",
     LENGTH(last_name) "expr2",
     NULLIF(LENGTH(first_name),LENGTH(last_name))
      result FROM employees;
  

  Producción :
  

• LNNVL() : LNNVL evalúa una condición cuando uno o ambos operandos de la condición pueden ser nulos. La función solo se puede usar en la cláusula WHERE de una consulta. Toma como argumento una condición y devuelve VERDADERO si la condición es FALSO o DESCONOCIDO y FALSO si la condición es VERDADERO.

  Sintaxis –

  LNNVL( condition(s) )
  

  Ejemplos –

  SELECT COUNT(*) FROM employees 
        WHERE commission_pct < .2; 
  

  Producción :
  

Ahora, los ejemplos anteriores no consideran a aquellos empleados que no tienen ninguna comisión.
Para incluirlos también usamos LNNVL()

SELECT COUNT(*) FROM employees 
  WHERE LNNVL(commission_pct >= .2); 

Producción :

NANVL( n1 , n2 )

SELECT bin_float, NANVL(bin_float,0)
  FROM nanvl_demo;

Referencia : Introducción a Oracle9i SQL (libro del volumen 1)