Search This Blog

Monday, July 22, 2019

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

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

1 comment:

Split A String By Coma (,)

Step 1.  create or replace type split_tbl as table of varchar2(32767); Step 2. create or replace function split (     p_list varchar2,...