Saturday, December 10, 2011

NVL, NVL2 & COALESCE functions

SQL has some functions which handles NULL values. This article is about NVL, NVL2 and COALESCE.

NVL requires two parameters, returns first if first is not null, returns second if first is NULL.

SQL> VARIABLE par1 NUMBER
SQL> SELECT NVL(:par1, 999999) AS "NVL result" FROM DUAL;

NVL result
----------
999999

SQL> BEGIN :par1 := 233; END;
2 /
PL/SQL procedure successfully completed.

SQL> SELECT NVL(:par1, 999999) AS "NVL result" FROM DUAL;

NVL result
----------
233

Two parameters in the NVL must be at least implicitly compatible. Otherwise, Oracle returns error.

SQL> VARIABLE
variable par1
datatype NUMBER

SQL> SELECT NVL(:par1,'SECOND PARAMETER') FROM DUAL;
SELECT NVL(:par1,'SECOND PARAMETER') FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT NVL(:par1,'0987654321') FROM DUAL;

NVL(:PAR1,'0987654321')
-----------------------
987654321

So, if we have table with number column which can contain NULL values, and we want receive "N/A" output for such values, we wolud to use such form as:

SQL> SELECT
2 NVL(TO_CHAR(column_name), 'N/A')
3 FROM ...
4 ;
FROM ...

NVL2 extends NVL. It requires three parameters and never returns first. Second parameter is returned if the first is not null. Third parameter is returned if the first is null.

SQL> VARIABLE par1 NUMBER
SQL> VARIABLE par2 VARCHAR2(10)
SQL> VARIABLE par3 VARCHAR2(10)

SQL> BEGIN
2 :par2 := 'SECOND OUT';
3 :par3 := 'THIRD OUT';
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> SELECT NVL2(:par1,:par2,:par3) AS "NVL2 result" FROM DUAL;

NVL2 result
--------------------------------
THIRD OUT

SQL> BEGIN :par1 := 0; END;
2 /

PL/SQL procedure successfully completed.

SQL> SELECT NVL2(:par1,:par2,:par3) AS "NVL2 result" FROM DUAL;

NVL2 result
--------------------------------
SECOND OUT

So, NVL2 requires that par2 and par3 must have the same type or be at least implicitly compatible. Type of the first parameter is not important. NVL2 never returns the first parameter!

And COALESCE returns the first non-null parameter. It must have at least two parameters.

SQL> SELECT COALESCE(1) FROM DUAL;
SELECT COALESCE(1) FROM DUAL
*
ERROR at line 1:
ORA-00938: not enough arguments for function

SQL> SELECT COALESCE(null,1) FROM DUAL;

COALESCE(NULL,1)
----------------
1

SQL> SELECT COALESCE(1,2) FROM DUAL;

COALESCE(1,2)
-------------
1

SQL> SELECT COALESCE(2,1) FROM DUAL;

COALESCE(2,1)
-------------
2

SQL> SELECT COALESCE(NULL, NULL, 3) FROM DUAL;

COALESCE(NULL,NULL,3)
---------------------
3

SQL> PRINT par1

PAR1
----------
0

SQL> PRINT par2

PAR2
--------------------------------
SECOND OUT

SQL> PRINT par3

PAR3
--------------------------------
THIRD OUT

SQL> SELECT COALESCE(:par2, :par3) FROM DUAL;

COALESCE(:PAR2,:PAR3)
--------------------------------
SECOND OUT

SQL> SELECT COALESCE(:par1, :par2) FROM DUAL;
SELECT COALESCE(:par1, :par2) FROM DUAL
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

COALESCE also needs parameters which have one type or can be implicitly converted.

No comments:

Post a Comment