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