Hi All.
Oracle Version: 19.15
All DDL/DML below.
Along with EMP table, consider we have an additional application table called user_filt_details, which stores report filters created by application users. For simplicity, assume all filter conditions retrieve only the ename, I need to generate an exploded report with the filter along with the enames retrieved by it .
_user_filt_details l_ooks like below:
Final Expected output is as below:
The below solution I thought of requires creating a table and populating it through a batch job every day, which would not capture any new filters added by users post batch run. I would appreciate if someone could advise a more Realtime/dynamic way of generating this output.
SET SERVEROUTPUT ON;
DECLARE
sql_stmt CLOB := NULL;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE filt_exploded';
FOR cur IN ( SELECT filt_name, filt_cond from user_filt_details)
LOOP
sql_stmt := q'{ INSERT INTO filt_exploded
SELECT vh.filt_name, vh.filt_cond, lat.ename
FROM user_filt_details vh, LATERAL( SELECT l.ename
FROM emp l
WHERE }' || cur.filt_cond
|| q'{ ) lat
WHERE vh.filt_name = '}' || cur.filt_name ||q'{'}'
;
DBMS_OUTPUT.PUT_LINE(sql_stmt);
EXECUTE IMMEDIATE sql_stmt;
COMMIT;
END LOOP;
END;
/
DDL/DMLs
CREATE TABLE user_filt_details (filt_name VARCHAR2(200), filt_cond CLOB);
INSERT INTO user_filt_details VALUES('my_jobs' , q'{JOB IN ('PRESIDENT', 'ANALYST')}');
INSERT INTO user_filt_details VALUES('sal_review' , q'{SAL <= 1000}');
INSERT INTO user_filt_details VALUES('dept_30_emps' , q'{DEPTNO = 30}');
COMMIT;
CREATE TABLE filt_exploded(filt_name VARCHAR2(200) , filt_cond CLOB, ename VARCHAR2(10) );
Please let me know if additional information is required.
Thank You!