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.