search

ohdsi-studies / PioneerWatchfulWaiting

This study is part of the joint PIONEER - EHDEN - OHDSI studyathon in March 2021, and aims to advance understanding of clinical management and outcomes of watchful waiting in prostate cancer.
Apache License 2.0
7 stars 18 forks source link

columns don't exist - 105.sql : ORA-00904: invalid identifier #61

Closed scossin closed 3 years ago

scossin commented 3 years ago

I have a java.sql.SQLSyntaxErrorException: ORA-00904: "C"."PROCEDURE_DATE" : invalid identifier

The error occurs here: https://github.com/ohdsi-studies/PioneerWatchfulWaiting/blob/ca047952645cd576ab9bd1137bff0a3af3627511/inst/sql/sql_server/105.sql#L721

My translated SQL: 

SELECT C.person_id, C.procedure_occurrence_id as event_id, C.procedure_date as start_date, (C.procedure_date + NUMTODSINTERVAL(1, 'day')) as END_DATE,
       C.procedure_concept_id as TARGET_CONCEPT_ID, C.visit_occurrence_id,
       C.procedure_date as sort_date
FROM (SELECT de.* 
  FROM OMOP.DRUG_EXPOSURE de
JOIN cgh7i9yrCodesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 14))
 ) C

When I execute manually to see all the columns in the C table:

SELECT C.*
FROM (SELECT de.* 
  FROM OMOP.DRUG_EXPOSURE de
JOIN cgh7i9yrCodesets codesets on ((de.drug_concept_id = codesets.concept_id and codesets.codeset_id = 14))
 ) C
-- End Drug Exposure Criteria

There are only colums from the OMOP.DRUG_EXPOSURE table. Since the comment before the sql query is " -- Begin Procedure Occurrence Criteria" I guess it's not supposed to be the DRUG_EXPOSURE table here.

keesvanbochove commented 3 years ago

This should be fixed with #63