msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

NVL2 in Oracle

Did you know that in addition to the function NVL, Oracle also has the function NVL2? which is quite different than its cousin NVL. So, what does NVL2 do?

Syntax:

NVL2 (expr1, expr2, expr3)

Purpose:

If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.

If the datatypes of expr2 and expr3 are different, Oracle converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary.

The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value’s datatype is VARCHAR2.

Example

The following example shows whether the income of each employee in department 30 is made up of salary plus commission, or just salary, depending on whether the comm column of emp is null or not.

SELECT ename, 
    NVL2(
        TO_CHAR(COMM), 
        'SAL & COMM', 
        'SAL') income
FROM emp 
WHERE deptno = 30;

ENAME      INCOME    
---------- ----------
ALLEN      SAL & COMM
WARD       SAL & COMM
MARTIN     SAL & COMM
BLAKE      SAL       
TURNER     SAL & COMM
JAMES      SAL 

Related articles:


Filed in Oracle, Tips on 30 Jun 05 | Tags: ,


Reader's Comments

  1. |

    You can also use DECODE, which (as an exception to the normal rule) will say that NULL = NULL

    DECODE(COMM, NULL, ‘SAL’, ‘SAL & COMM’)

    http://thinkoracle.blogspot.com/2005/06/decode.html

    But, keep in mind that NVL and NVL2 seem to have a performance hit over the more verbose manual NULL check.

    http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

    orig article: http://www.oracledba.co.uk/tips/plsql_nvl_costs.htm discussion: http://www.phpbbserver.com/phpbb/viewtopic.php?t=220&mforum=dizwellforum

    Cheers, Robert

  2. |

    Robert, Thanks for the extra tips and links. I see you are a fan of Tom Kyte. I am too. I have learnt a lot from him, and still learning…

    Cheers!

  3. |

    heheh no loves for PL/SQL I guess

    http://groups.google.com/group/comp.databases.oracle.server/msg/165d24ffccb982e7?dmode=source&hl=en