Listagg with apex_string.split Query

Topic Introduction: In this tutorial, we will discuss the use of apex_string.split on Oracle SQL. Sometimes we need to use the LISTAGG function to show multiple row values into one value. but if we want to filter by this column which is retrieved by using LISTAGG in this case we can use apex_string.split. To be more clear follow the query below.

LISTAGG with apex_string.split Query

select VISIT_ID,

       COM_ID,

       VISIT_DATE,

       VISITOR_EMPID,

       VISIT_PERSON_NM,

       VISIT_PERSON_DESIG,

       VISIT_PERSON_PHONE,

       VISIT_PERSON_EMAIL,

       VISIT_PERSON_ADD,

       VISIT_REMARKS,      

       MANAGER

from 

(select VISIT_ID,

       COM_ID,

       VISIT_DATE,

       VISITOR_EMPID,

       VISIT_PERSON_NM,

       VISIT_PERSON_DESIG,

       VISIT_PERSON_PHONE,

       VISIT_PERSON_EMAIL,

       VISIT_PERSON_ADD,

       VISIT_REMARKS,      

       (select LISTAGG(ITEM_MANAGER, ',') WITHIN GROUP (ORDER BY VISIT_MASTER_ID)  from VISIT_INFO_DETAIL WHERE VISIT_MASTER_ID=VISIT_ID) MANAGER

     

from VISIT_INFO_MASTER

order by CREATED_ON desc

)

WHERE :APP_USER IN  (SELECT COLUMN_VALUE FROM TABLE(apex_string.split(MANAGER,',')))