GENERAL Functions:
NVL(column_name, m) - Returns m if column_name value is NULL
-
SELECT NVL(empno,0) FROM emp -- returns 0 if empno is NULL
-
NVL2(column_name,c1,c2) - Returns c1 if column_name IS NOT NULL and c2 if column_name IS NULL
-
SELECT NVL2(empno,empno,'NO EID') from dual -- returns empno if empno IS NOT NULL else returns 'NO EID'
-
NULLIF(column_name,value) - Returns NULL, IF column_name = value
-
SELECT NULLIF(empno,1) FROM emp -- returns NULL IF empno = 1
-
COALESCE(column1,column2,column3,column4) - Returns columns2 if column1 IS NULL, else returns column3 if column2 is also NULL and so on. All the columns should be of same data type
-
SELECT COALESCE(ename,first_name,last_name,'NO NAME') from emp -- returns first_name IF ename IS NULL, IF first_name IS also NULL, then returns last_name, IF last_name IS also NULL, then returns 'NO NAME'
-
DECODE(column_name,val1,ret_val,val2,ret_val2,ret_ val3) - Returns ret_val1 IF column_name = val1 ELSE Returns ret_Val2 IF column_name = val2 ELSE Returns ret_val3
-
SELECT DECODE(empno,1,'PM',2,'BM','EMP') FROM emp -- returns 'PM' if empno = 1 ELSE IF empno = 2 then returns 'BM' ELSE returns 'EMP'
-
CASE(column_name) - CASE can be used in PLSQL as well as SQL statement.
-
SELECT empno,ename,
-
(CASE WHEN (empno = 1) THEN 'PM'
-
WHEN (empno = 2) THEN 'BM'
-
ELSE 'EMP' END) Position
-
FROM emp
-
