Sql error - PheValuator .createEvaluationCohort Creating evaluation cohort on server from sql

[gowthamrao]

Invalid operation: table name "obs2" specified more than once;

CREATE TABLE scratch.test_eval_6_d6cy5vqr DISTKEY(SUBJECT_ID) AS SELECT CAST(0 AS BIGINT) as COHORT_DEFINITION_ID, person_id as SUBJECT_ID , DATEADD(day,CAST(0 as int),visit_start_date) COHORT_START_DATE, DATEADD(day,CAST(1 as int),visit_start_date) COHORT_END_DATE FROM (select v.person_id, FIRST_VALUE(visit_start_date) OVER (PARTITION BY v.person_id ORDER BY MD5(RANDOM()::TEXT || GETDATE()::TEXT) ROWS UNBOUNDED PRECEDING) visit_start_date, ROW_NUMBER() OVER (ORDER BY MD5(RANDOM()::TEXT || GETDATE()::TEXT) ) rn from cdm.visit_occurrence v JOIN cdm.observation_period obs on v.person_id = obs.person_id AND v.visit_start_date >= DATEADD(d,CAST(365 as int),obs.observation_period_start_date) AND v.visit_start_date <= DATEADD(d,CAST(-30 as int),obs.observation_period_end_date) join ( select person_id, datediff(day, min(observation_period_start_date), min(observation_period_end_date)) lenPd, min(observation_period_start_date) observation_period_start_date, min(observation_period_end_date) observation_period_end_date, count(observation_period_id) cntPd from cdm.observation_period group by person_id) obs2 on v.person_id = obs2.person_id and v.visit_start_date >= obs2.observation_period_start_date and v.visit_start_date <= obs2.observation_period_end_date and lenPd >= 730 and cntPd = 1 join cdm.person p on v.person_id = p.person_id and EXTRACT(YEAR FROM visit_start_date) - year_of_birth >= 0 and EXTRACT(YEAR FROM visit_start_date) - year_of_birth <= 120 and gender_concept_id in (8507,8532) join ( select person_id, datediff(day, min(observation_period_start_date), min(observation_period_end_date)) lenPd, min(observation_period_start_date) observation_period_start_date, min(observation_period_end_date) observation_period_end_date, count(observation_period_id) cntPd from cdm.observation_period group by person_id) obs2 on v.person_id = obs2.person_id and v.visit_start_date >= obs2.observation_period_start_date and v.visit_start_date <= obs2.observation_period_end_date and lenPd >= 730 and cntPd = 1 where visit_start_date >= cast('19001010' AS DATE) and visit_start_date <= cast('21000101' AS DATE) and v.visit_concept_id in (9201,9202,9203,581477,262) and datediff(day, visit_start_date, visit_end_date) >= 0 and 11(9(v.visit_occurrence_id/9)/11) = v.visit_occurrence_id ) negs where rn <= cast('2000000' as bigint) union select 0 as COHORT_DEFINITION_ID, SUBJECT_ID, cp.COHORT_START_DATE COHORT_START_DATE, DATEADD(day,CAST(1 as int),cp.COHORT_START_DATE) COHORT_END_DATE from #cohort_person cp join cdm.observation_period o on cp.SUBJECT_ID = o.person_id and cp.COHORT_START_DATE >= o.observation_period_start_date and cp.COHORT_START_DATE <= o.observation_period_end_date where rn <= 100 union select 6 as COHORT_DEFINITION_ID, SUBJECT_ID, cp.COHORT_START_DATE COHORT_START_DATE, DATEADD(day,CAST(1 as int),cp.COHORT_START_DATE) COHORT_END_DATE from #cohort_person cp join cdm.observation_period o on cp.SUBJECT_ID = o.person_id and cp.COHORT_START_DATE >= o.observation_period_start_date and cp.COHORT_START_DATE <= o.observation_period_end_date where rn <= 100

[gowthamrao]

This was from a plain vanilla run with default (0) for evaluation and model population cohorts

[swerdel]

Would you send me the script you used to run the analysis? Thanks.

[gowthamrao]

i found it hard to show reproducible code, but

https://github.com/OHDSI/PheValuator/blob/ff3f641a950df3fea8eb1ee55a82c6eb4ed22652/inst/sql/sql_server/CreateCohortsAcuteEvaluation.sql#L71

and

https://github.com/OHDSI/PheValuator/blob/ff3f641a950df3fea8eb1ee55a82c6eb4ed22652/inst/sql/sql_server/CreateCohortsAcuteEvaluation.sql#L91

there are two obs2 within the same CTE - i think thats what the error is referring to