Hi all, I have just started working with CDM.
While I was trying to execute ‘Study 1 - Treatment Pathways’, I faced a problem.
In the MainAnalysis.R code, when I run ‘executeSql(conn, readSql(htnSqlFile))’ line, the progress bar soon reaches 21%, but does not progress further.
So I tried running the ‘htnsql’ script in ORACLE directly, but stopped at the specific query below.
INSERT INTO HIRCstudyName_IndexCohort (PERSON_ID, INDEX_DATE, COHORT_END_DATE, OBSERVATION_PERIOD_START_DATE, OBSERVATION_PERIOD_END_DATE)
SELECT person_id, INDEX_DATE,COHORT_END_DATE, observation_period_start_date, observation_period_end_date
FROM (SELECT ot.PERSON_ID, ot.INDEX_DATE, MIN(e.END_DATE) as COHORT_END_DATE, ot.OBSERVATION_PERIOD_START_DATE, ot.OBSERVATION_PERIOD_END_DATE, ROW_NUMBER() OVER (PARTITION BY ot.PERSON_ID ORDER BY ot.INDEX_DATE) as RowNumber
FROM (SELECT dt.PERSON_ID, dt.DRUG_EXPOSURE_START_DATE as index_date, op.OBSERVATION_PERIOD_START_DATE, op.OBSERVATION_PERIOD_END_DATE
FROM (SELECT de.PERSON_ID, de.DRUG_CONCEPT_ID, de.DRUG_EXPOSURE_START_DATE
FROM (SELECT d.PERSON_ID, d.DRUG_CONCEPT_ID, d.DRUG_EXPOSURE_START_DATE,
COALESCE(d.DRUG_EXPOSURE_END_DATE, (d.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(d.DAYS_SUPPLY, ‘day’)), (d.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, ‘day’))) as DRUG_EXPOSURE_END_DATE,
ROW_NUMBER() OVER (PARTITION BY d.PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as RowNumber
FROM CDM.DRUG_EXPOSURE d
JOIN CDM.CONCEPT_ANCESTOR ca
on d.DRUG_CONCEPT_ID = ca.DESCENDANT_CONCEPT_ID and ca.ANCESTOR_CONCEPT_ID in (21600381,21601461,21601560,21601664,21601744,21601782)
) de
JOIN CDM.PERSON p on p.PERSON_ID = de.PERSON_ID
WHERE de.RowNumber = 1
) dt
JOIN CDM.observation_period op
on op.PERSON_ID = dt.PERSON_ID and (dt.DRUG_EXPOSURE_START_DATE between op.OBSERVATION_PERIOD_START_DATE and op.OBSERVATION_PERIOD_END_DATE)
WHERE (op.OBSERVATION_PERIOD_START_DATE + NUMTODSINTERVAL(365, ‘day’)) <= dt.DRUG_EXPOSURE_START_DATE AND (dt.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1095, ‘day’)) <= op.OBSERVATION_PERIOD_END_DATE
) ot
join
(SELECT PERSON_ID, (EVENT_DATE + NUMTODSINTERVAL(-31, ‘day’)) as END_DATE – subtract 30 days to end dates to resove back to the ‘true’ dates
FROM (SELECT PERSON_ID, EVENT_DATE, EVENT_TYPE, START_ORDINAL,
ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY EVENT_DATE, EVENT_TYPE) AS EVENT_ORDINAL,
MAX(START_ORDINAL) OVER (PARTITION BY PERSON_ID ORDER BY EVENT_DATE, EVENT_TYPE ROWS UNBOUNDED PRECEDING) as STARTS
FROM (SELECT PERSON_ID, DRUG_EXPOSURE_START_DATE AS EVENT_DATE, 1 as EVENT_TYPE, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as START_ORDINAL
FROM (SELECT d.PERSON_ID, d.DRUG_CONCEPT_ID, d.DRUG_EXPOSURE_START_DATE,
COALESCE(d.DRUG_EXPOSURE_END_DATE, (d.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(d.DAYS_SUPPLY, ‘day’)), (d.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, ‘day’))) as DRUG_EXPOSURE_END_DATE,
ROW_NUMBER() OVER (PARTITION BY d.PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as RowNumber
FROM CDM.DRUG_EXPOSURE d
JOIN CDM.CONCEPT_ANCESTOR ca
on d.DRUG_CONCEPT_ID = ca.DESCENDANT_CONCEPT_ID and ca.ANCESTOR_CONCEPT_ID in (21600381,21601461,21601560,21601664,21601744,21601782)
)
cteExposureData
UNION ALL
select PERSON_ID, (DRUG_EXPOSURE_END_DATE + NUMTODSINTERVAL(31, ‘day’)), 0 as EVENT_TYPE, NULL
FROM (SELECT d.PERSON_ID, d.DRUG_CONCEPT_ID, d.DRUG_EXPOSURE_START_DATE,
COALESCE(d.DRUG_EXPOSURE_END_DATE, (d.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(d.DAYS_SUPPLY, ‘day’)), (d.DRUG_EXPOSURE_START_DATE + NUMTODSINTERVAL(1, ‘day’))) as DRUG_EXPOSURE_END_DATE,
ROW_NUMBER() OVER (PARTITION BY d.PERSON_ID ORDER BY DRUG_EXPOSURE_START_DATE) as RowNumber
FROM CDM.DRUG_EXPOSURE d
JOIN CDM.CONCEPT_ANCESTOR ca
on d.DRUG_CONCEPT_ID = ca.DESCENDANT_CONCEPT_ID and ca.ANCESTOR_CONCEPT_ID in (21600381,21601461,21601560,21601664,21601744,21601782)
) cteExposureData
) RAWDATA
) E
WHERE 2 * E.STARTS - E.EVENT_ORDINAL = 0
) e on e.PERSON_ID = ot.PERSON_ID and e.END_DATE >= ot.INDEX_DATE
GROUP BY ot.PERSON_ID, ot.INDEX_DATE, ot.OBSERVATION_PERIOD_START_DATE, ot.OBSERVATION_PERIOD_END_DATE
) r
WHERE r.RowNumber = 1
;