Data Model Nested Queries
BIP Periodic reports follow the model of Nested queries.
Consider the PBRER 6.2 Cumulative Summary Tabulation queries.
Query 1: Q1_PBRER62
SELECT ct.soc g1pbrer62soc, COUNT (ct.CASE_STUDY_DRUG) cnt_study_drug, COUNT (ct.CASE_COMPARATOR) cnt_case_comparator, COUNT (ct.case_blinded) cnt_blinded, COUNT (ct.case_placebo) cnt_placebo, COUNT (ct.case_nosdgiven) cnt_nosdgiven, COUNT (ct.case_num) cnt_case_num FROM v$rpt_clinicalsummary ct WHERE ct.REG_REPORT_ID = pkg_agg_rpt.f_get_report_id AND (ct.Sec62cumflag = 'Y' AND NVL(ct.sec63nonintcumflag,'N') <> 'Y') AND (ct.casetype = 'C' AND ct.eventseriousflag = 'Y') AND ct.Clinicaldrugrole != 6 GROUP BY ct.ev_socdisplaynbr,ct.soc ORDER BY ct.ev_socdisplaynbr,ct.soc;
In QUERY 1, the column SOC is given an alias g1pbrer62soc
Grouping: g1 -> Group 1
pbrer62 -> Report section
soc -> Column name
Sorting: Order by SOCDISPLAYNBR and SOC. Users can modify the sorting columns by changing the data model queries for each group.
Query 2: Q2_PBRER62
SELECT ct.reaction g2pbrer62reaction, COUNT (ct.CASE_STUDY_DRUG) cnt_study_drug, COUNT (ct.CASE_COMPARATOR) cnt_case_comparator, COUNT (ct.case_blinded) cnt_blinded, COUNT (ct.case_placebo) cnt_placebo, COUNT (ct.case_nosdgiven) cnt_nosdgiven, COUNT (ct.case_num) cnt_case_num FROM v$rpt_clinicalsummary ct WHERE ct.REG_REPORT_ID = pkg_agg_rpt.f_get_report_id AND (ct.Sec62cumflag = 'Y' AND NVL(ct.sec63nonintcumflag,'N') <> 'Y') AND (ct.casetype = 'C' AND ct.eventseriousflag = 'Y') AND ct.clinicaldrugrole != 6 AND ct.soc = :g1pbrer62soc GROUP BY ct.reaction ORDER BY ct.reaction;
In the above QUERY 2 the column REACTION given an alias "g2pbrer62reaction"
Grouping:g2 -> Group 2
pbrer62 -> Report section
reaction -> Column name.
Sorting: Order by Event Reaction, Users can modify the sorting columns by changing the data model queries for each group.
In Query 2, the group1 column SOC is passed in the where condition AND ct.soc = :g1pbrer62soc.
Hence, Query 2 fetches rows only for the SOCs from Query 1. This NESTED query model is used throughout BIP Periodic reports.