Single-row functions in ORACLE

Oracle 9i has many old and new single-row functions that are very useful. Some of them that I find very interesting are:



NVL(x1, x2)
NULLs are of great importance and I am pretty sure that I will get atleast one question on NULLs in the test. One of the ways that NULL values are handled in ORACLE is by using the function NVL(x1,x2). This function takes 2 values x1 and x2 and return x2 if x1 is NULL else it returns x1. Eg., the following query will return the employee name and salary+bonus for each employee:

SELECT ename, salary + NVL(bonus,0) FROM emp

NVL2(x1,x2,x3)
NVL2 is also used to handle NULL values. It takes 3 values (x1,x2,x3) and return x3 if x1 is NULL and x2 if x1 is not NULL. So we can rewrite the above query as follows:

SELECT ename, NVL2(bonus, salary+bonus, salary) from EMP

DECODE
Decode is used to incorporate IF.. THEN.. ELSE functionality in Oracle. Eg., the following query will display the employee name and the department name:

SELECT ename,
DECODE(deptno
, 10,'Accounting'
,20,'Administration'
,'Other')
FROM emp

- Raza