search

PEDSnet / pedsnetdcc

CLI tool for PEDSnet data coordinating center ETL tasks
Other
0 stars 0 forks source link

Visit Occurrence self joins twice, no table alias causes error #93

Closed eceowl closed 7 years ago

eceowl commented 7 years ago

The visit_occurrence table has a new column preceding_visit_occurrence_id, this is the second self join that visit_occurrence attempts during the transform process. This causes the following error:

pedsnetdcc.utils.DatabaseError: creating transformed copy of table visit_occurrence: table name "visit_occurrence_ids" specified more than once

The query being run is:

CREATE UNLOGGED TABLE colorado_pedsnet_transformed.visit_occurrence AS SELECT 
visit_occurrence.admitting_source_concept_id, visit_occurrence.admitting_source_value, visit_occurrence.discharge_to_concept_id, visit_occurrence.discharge_to_source_value, visit_occurrence.visit_concept_id, 
visit_occurrence.visit_end_date, visit_occurrence.visit_end_time, visit_occurrence.visit_source_concept_id, visit_occurrence.visit_source_value, visit_occurrence.visit_start_date, 
visit_occurrence.visit_start_time, visit_occurrence.visit_type_concept_id, months_in_interval(person.time_of_birth, visit_occurrence.visit_start_time) 
AS visit_start_age_in_months, concept_1.concept_name AS admitting_source_concept_name, concept_2.concept_name AS discharge_to_concept_name, concept_3.concept_name AS visit_concept_name, 
concept_4.concept_name AS visit_source_concept_name, concept_5.concept_name AS visit_type_concept_name, 'colorado'::varchar(32) AS site, visit_occurrence_ids.dcc_id AS visit_occurrence_id, 
visit_occurrence.visit_occurrence_id AS site_id, care_site_ids.dcc_id AS care_site_id, visit_occurrence_ids.dcc_id AS preceding_visit_occurrence_id, person_ids.dcc_id AS person_id, provider_ids.dcc_id 
AS provider_id
FROM visit_occurrence 
JOIN person ON person.person_id = visit_occurrence.person_id 
LEFT OUTER JOIN concept AS concept_1 ON concept_1.concept_id = admitting_source_concept_id 
LEFT OUTER JOIN concept AS concept_2 ON concept_2.concept_id = discharge_to_concept_id 
LEFT OUTER JOIN concept AS concept_3 ON concept_3.concept_id = visit_concept_id 
LEFT OUTER JOIN concept AS concept_4 ON concept_4.concept_id = visit_source_concept_id 
LEFT OUTER JOIN concept AS concept_5 ON concept_5.concept_id = visit_type_concept_id 
JOIN visit_occurrence_ids ON visit_occurrence.visit_occurrence_id = visit_occurrence_ids.site_id 
LEFT OUTER JOIN care_site_ids ON visit_occurrence.care_site_id = care_site_ids.site_id 
LEFT OUTER JOIN visit_occurrence_ids ON visit_occurrence.preceding_visit_occurrence_id = visit_occurrence_ids.site_id 
JOIN person_ids ON visit_occurrence.person_id = person_ids.site_id 
LEFT OUTER JOIN provider_ids ON visit_occurrence.provider_id = provider_ids.site_id

In order to fix this, we need to add aliases to the join tables.

eceowl commented 7 years ago

Fixed in #94