GENERAL FUNCTIONS

[amitpatel66]

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