DBMS:
oracle

Error:
java.sql.SQLException: ORA-12801: error signaled in parallel query server P007
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_1155298549$" too small


SQL:
CREATE TABLE g9evagcaqualified_events
 AS
SELECT
event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id
FROM
(SELECT pe.event_id, pe.person_id, pe.start_date, pe.end_date, pe.op_start_date, pe.op_end_date, row_number() over (partition by pe.person_id order by pe.start_date ASC) as ordinal, cast(pe.visit_occurrence_id as NUMBER(19)) as visit_occurrence_id
  FROM (SELECT P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as NUMBER(19)) as visit_occurrence_id
FROM (SELECT E.person_id, E.start_date, E.end_date,
         row_number() OVER (PARTITION BY E.person_id ORDER BY E.sort_date ASC, E.event_id) ordinal,
         OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as NUMBER(19)) as visit_occurrence_id
  FROM (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 0)
 ) C
-- End Condition Occurrence Criteria
  UNION ALL
select PE.person_id, PE.event_id, PE.start_date, PE.end_date, PE.visit_occurrence_id, PE.sort_date FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) PE
JOIN (SELECT 0 as index_id, person_id, event_id
FROM (SELECT E.person_id, E.event_id 
  FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) E
  INNER JOIN
  (SELECT 0 as index_id, cc.person_id, cc.event_id
FROM (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 0)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= (P.START_DATE + NUMTODSINTERVAL(1, 'day')) AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(365, 'day'))  ) cc 
GROUP BY cc.person_id, cc.event_id
HAVING COUNT(cc.event_id) >= 1
-- End Correlated Criteria
  UNION ALL
-- Begin Criteria Group
SELECT 1  index_id, person_id, event_id
FROM (SELECT E.person_id, E.event_id 
  FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) E
  INNER JOIN
  (SELECT 0 as index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 13)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 1  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 6)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 2  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 10)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= (P.START_DATE + NUMTODSINTERVAL(-365, 'day')) AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 3  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 8)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= (P.START_DATE + NUMTODSINTERVAL(-365, 'day')) AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
select 4  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 11)
 ) C
-- End Drug Exposure Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= (P.START_DATE + NUMTODSINTERVAL(-365, 'day')) AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
   ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
  GROUP BY E.person_id, E.event_id
  HAVING COUNT(index_id) = 5
 ) G
-- End Criteria Group
  UNION ALL
-- Begin Criteria Group
select 2  index_id, person_id, event_id
FROM (SELECT E.person_id, E.event_id 
  FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) E
  INNER JOIN
  (SELECT 0 as index_id, cc.person_id, cc.event_id
FROM (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 12)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= (P.START_DATE + NUMTODSINTERVAL(-365, 'day')) AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc 
GROUP BY cc.person_id, cc.event_id
HAVING COUNT(cc.event_id) >= 1
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 1  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 2)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 2  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 6)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 3  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 8)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(0, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
SELECT 4  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.condition_occurrence_id as event_id, C.condition_start_date as start_date, COALESCE(C.condition_end_date, (C.condition_start_date + NUMTODSINTERVAL(1, 'day'))) as end_date,
  C.visit_occurrence_id, C.condition_start_date as sort_date
FROM (SELECT co.* 
  FROM OMOP_SYNONYM_SCHEMA.CONDITION_OCCURRENCE co
  JOIN g9evagcaCodesets cs on (co.condition_concept_id = cs.concept_id and cs.codeset_id = 10)
 ) C
-- End Condition Occurrence Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
  UNION ALL
-- Begin Correlated Criteria
select 5  index_id, p.person_id, p.event_id
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) p
LEFT JOIN (SELECT p.person_id, p.event_id 
FROM (SELECT Q.person_id, Q.event_id, Q.start_date, Q.end_date, Q.visit_occurrence_id, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date
FROM (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 3)
 ) C
-- End Drug Exposure Criteria
 ) Q
JOIN OMOP_SYNONYM_SCHEMA.OBSERVATION_PERIOD OP on Q.person_id = OP.person_id 
  and OP.observation_period_start_date <= Q.start_date and OP.observation_period_end_date >= Q.start_date
 ) P
JOIN (SELECT C.person_id, C.drug_exposure_id as event_id, C.drug_exposure_start_date as start_date,
       COALESCE(C.DRUG_EXPOSURE_END_DATE, (DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(C.DAYS_SUPPLY, 'day')), (C.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, 'day'))) as end_date,
       C.visit_occurrence_id,C.drug_exposure_start_date as sort_date
FROM (SELECT de.* 
  FROM OMOP_SYNONYM_SCHEMA.DRUG_EXPOSURE de
JOIN g9evagcaCodesets cs on (de.drug_concept_id = cs.concept_id and cs.codeset_id = 11)
 ) C
-- End Drug Exposure Criteria
 ) A on A.person_id = P.person_id  AND A.START_DATE >= P.OP_START_DATE AND A.START_DATE <= P.OP_END_DATE AND A.START_DATE >= (P.START_DATE + NUMTODSINTERVAL(-365, 'day')) AND A.START_DATE <= (P.START_DATE + NUMTODSINTERVAL(7, 'day'))  ) cc on p.person_id = cc.person_id and p.event_id = cc.event_id
GROUP BY p.person_id, p.event_id
HAVING COUNT(cc.event_id) = 0
-- End Correlated Criteria
   ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
  GROUP BY E.person_id, E.event_id
  HAVING COUNT(index_id) = 6
 ) G
-- End Criteria Group
   ) CQ on E.person_id = CQ.person_id and E.event_id = CQ.event_id
  GROUP BY E.person_id, E.event_id
  HAVING COUNT(index_id) > 0
 ) G
-- End Criteria Group
 ) AC on AC.person_id = pe.person_id and AC.event_id = pe.event_id
   ) E
	JOIN OMOP_SYNONYM_SCHEMA.observation_period OP on E.person_id = OP.person_id and E.start_date >=  OP.observation_period_start_date and E.start_date <= op.observation_period_end_date
    WHERE (OP.OBSERVATION_PERIOD_START_DATE + NUMTODSINTERVAL(0, 'day')) <= E.START_DATE AND (E.START_DATE + NUMTODSINTERVAL(0, 'day')) <= OP.OBSERVATION_PERIOD_END_DATE
 ) P
  WHERE P.ordinal = 1
-- End Primary Events
 ) pe
 ) QE
 

R version:
R version 4.2.3 (2023-03-15)

Platform:
x86_64-pc-linux-gnu

Attached base packages:
- stats
- graphics
- grDevices
- datasets
- utils
- methods
- base

Other attached packages:
- Strategus (0.1.0)
- ParallelLogger (3.3.0)
- CohortGenerator (0.8.1)
- DatabaseConnector (6.2.4)
- keyring (1.3.1)
- openssl (2.1.1)
- dplyr (1.1.3)
- R6 (2.5.1)