Uncommon Functions and Objects In Oracle SQL and PL/SQL

1. TO_CHAR(Date,'fmDay Month fmDD, YYYY'): 'fm' prefix can be remove Embedded spaces       from value.


2. NVL2( value , value_if_not_null, value_if_null )

3. In NVL and NVL2

NVL2(inv_date,'Pending','Incomplete')               
=> True (value_if_not_null and value_if_null are same type)

NVL2(inv_amt,inv_date,'Not Available')                                       False

NVL2(inv_date,sysdate-inv_date,sysdate)                   
=> True (value_if_not_null and value_if_null are same type)

NVL2(inv_amt,inv_amt*.25,'Not Available')                                False

NVL(ADD_MONTHS(END_DATE,1),SYSDATE)             
=> True (value and value_if_null are same type )

TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))               False

NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')     False

NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')
=> True (value_if_null is convert to value_if_null type)

NVL(TO_CHAR(cust_credit_limit*.15),'Not Available')       
=>  True (value_if_null is convert to value_if_null type)

TO_CHAR(NVL(cust_credit_limit*.15,'Not Available'))             False

NVL(cust_credit_limit*.15,'Not Available')                                  False

NVL(cust_credit_limit,'Not Available')                                         False

4. NULLIF
If both value is same then return null or retrun first value.
SELECT NULLIF(1, 1) FROM emp; Return : null
SELECT NULLIF(1, 2) FROM emp; Return : 1
SELECT NULLIF(null, 2) FROM emp; Return : null

5. COALESCE
COALESCE (value1, value2, value3) is equal to

CASE
WHEN value1 is NOT NULL THEN value1
WHEN value2 is NOT NULL THEN value2
Else value3
END