Various Type of Selection

Topic Introduction: In this tutorial, we will discuss various types of Selection. Selection is the most important part of Oracle SQL. To get targeted data we give various types of restrictions or conditions on where clause. It can be filtered by Data, Character, and Number. Sometimes users need to filter by multiple values on one parameter. Here are some examples of filtering below. 

 Between Start Date and End Date

WHERE JOIN_DATE>=DECODE(:SDATE,NULL,JOIN_DATE,:SDATE)

AND JOIN_DATE<=DECODE(:EDATE,NULL,JOIN_DATE,:EDATE);

AND (:SDATE IS NULL OR TRUNC(FFIM_DATE)>=:SDATE)

AND (:EDATE IS NULL OR TRUNC(FFIM_DATE)<=:EDATE)

WHERE TRUNC(JOIN_DATE) BETWEEN DECODE(:SDATE,NULL,TRUNC(JOIN_DATE),:SDATE) AND DECODE(:EDATE,NULL,TRUNC(JOIN_DATE),:EDATE);

Where 

WHERE NVL(DEPARTMENT_ID,0)=DECODE(:DEPTID,NULL,NVL(DEPARTMENT_ID,0),:DEPTID);

WHERE DEPARTMENT_ID=DECODE(:DEPTID,NULL,DEPARTMENT_ID,:DEPTID);

WHERE DECODE(SHIFT,'DAY','1','NIGHT','2',SHIFT) = DECODE(:SFT, NULL, DECODE(SHIFT,'DAY','1','NIGHT','2',SHIFT), :SFT);

Like Not Like At Same Query

WHERE UPPER(DEPARTMENT_NAME) LIKE DECODE(UPPER(:WITHSPEC),NULL,UPPER(DEPARTMENT_NAME),'%'||UPPER(:WITHSPEC)||'%')

AND UPPER(DEPARTMENT_NAME) NOT LIKE '%'||NVL(UPPER(:WITHOUTSPEC),'ABCDEFGHXYZ0')||'%'

Like

AND ITEM_NAME LIKE '%'||:SPEC||'%'

WHERE TRIM(regexp_substr(ITEM_NAME, '\(..*$')) LIKE '%'||TRIM(regexp_substr(:SPEC, '\(..*$'))||'%' 

Join Table Condition by Value

AND APP_FLAG(+)=1

AND SAL_TYPE=SOFT_CODE(+)

AND NVL(HARD_CODE,'PR')='PR'

Others

AND NVL(APP_FLAG,0)=DECODE(:APP,NULL,NVL(APP_FLAG,0),:APP)

AND TRUNC(JOIN_DATE,'MONTH')=ADD_MONTHS(TRUNC(TO_DATE(:EDATE),'MONTH'),-1)