SQL Tutorial : DRL / DQL - GENERAL FUNCTION

SQL Tutorial : DRL / DQL - GENERAL FUNCTION

Let's learn about next topic: GENERAL FUNCTION

GENERAL FUNCTIONS:

**********************

- NVL, NVL2, COALESCE, NULLIF

NVL(E1,E2)

**********

- IF E1 IS NULL THEN E2

ELSE E1

- E1 AND E2 MUST MATCH IN DATATYPE


eg:
- SELECT LAST_NAME,SALARY,NVL(COMMISSION_PCT,0)

FROM EMPLOYEES

- SELECT LAST_NAME,SALARY,NVL(COMMISSION_PCT,0),

SALARY+SALARY*NVL(COMMISSION_PCT,0) "MONTHLY COMP"

FROM EMPLOYEES

- SELECT LAST_NAME,SALARY,NVL(COMMISSION_PCT,'NO COMM')

FROM EMPLOYEES

NVL2(E1,E2,E3)

**************

- IF E1 IS NULL THE E3

ELSE E2

E2 AND E3

- MUST MATCH IN DATA TYPE

- SELECT LAST_NAME,NVL2(COMMISSION_PCT ,COMMISSION_PCT,0)

FROM EMPLOYEES

- SELECT LAST_NAME,NVL2(COMMISSION_PCT ,'COMM','NO COMM')

 FROM EMPLOYEES

NULLIF ( E1 ,E2)

***************

- IF E1 =E2 THEN NULL ELSE E1

- SELECT NULLIF(5,3)

FROM DUAL

COALESCE( E1,E2,E3,....)

*********************

- RETURNS THE FIRST NOT NULL VALUES

- SELECT COMMISSION_PCT,MANAGER_ID,SALARY ,

COALESCE(COMMISSION_PCT,MANAGER_ID,SALARY )

FROM EMPLOYEES