Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL NVL2 Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: NVL2

Definition:
The Oracle PL/SQL NVL2 function is a null handling function which returns alternate values for both NULL and NOT NULL values of a column. It accepts three arguments, the first being the column name, the second is the alternate value of the column when it is NOT NULL, and the third is the alternate value when the column value is NULL.

Example Syntax:

NVL2(n,x,y)


x is alternate value when n is NOT NULL
y is alternate value when n is NULL.

Note: x and y may have any data type except LONG. The data type of 'x' determines the data type of the output. If it is a character data type, then 'y' is converted to character before comparison, if 'y' is not a constant data. If 'x' is a number, then Oracle casts both 'x' and 'y' to the data type of the higher precedence.

Example Usage:

The SQL query below displays the message 'JOB' for employees who have a valid JOB ID. For those who don't have a JOB ID assigned, the query displays BENCH.

SQL> SELECT EMPNO,JOB, NVL2(JOB, 'JOB', 'BENCH')
FROM EMPLOYEE
/

EMPNO JOB NVL2(
---------- ---------- -----
100 MGR JOB
110 TECH JOB
120 BENCH
130 BENCH
140 MGR JOB
150 TECH JOB
160 BENCH
170 HR JOB
180 MGR JOB

9 rows selected.



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 68 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?