Oracle SQL DECODE, NVL and NVL 2 Functions

The DECODE function can be thought of as an inline IF statement. DECODE takes four or more expressions as arguments.

Syntax Of the DECODE

DECODE(Expr1, Expr2, Expr3, Expr4)

IF Expr1 = Expr2 THEN Expr3 ELSE Expr4

Expressions can be a column, a literal, a function, or even a subquery.

Let’s look at a simple example using DECODE:

SELECT lname,  DECODE(manager_emp_id, NULL, 'MANAGER', 'NON-MANAGER') emp_type

FROM employee;

NVL and NVL2

The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE.

Syntax of NVL2 Function

NVL2(Expr1, Expr2, Expr3)

IF Expr1 IS NULL THEN Expr3 ELSE Expr2

Example for NVL2:

SELECT lname,  NVL2(manager_emp_id, 'NON-MANAGER', 'MANAGER') emp_type

FROM employee;

Syntax of NVL

NVL(Expr1,Expr2)

IF Expr1 is NULL THEN Expr2

  

Example:

SELECT supplier_id

NVL(supplier_desc, supplier_name)

FROM suppliers;