Friends,
Came across a new error that’s got me a little befuddled. Anyone seen this…
Creating exposure and outcome cohorts
Connecting using Redshift driver
Creating cohort: …
Warning: Parameter ‘vocabulary_database_schema’ not found in SQL
|==== | 5%Error: Error executing SQL:
java.sql.SQLException: Amazon Invalid operation: syntax error at or near “)”
Position: 699;
Here’s the background. I received a ZIP study package from another investigator. We had two iterations of a study design we’re testing for network dissemination. The problematic package was built using public ATLAS (artifact can be found here: http://atlas-demo.ohdsi.org/#/estimation/cca/232). I could successfully run her other package also built with the same public ATLAS without issue (artifact can be found here: http://atlas-demo.ohdsi.org/#/estimation/cca/224). I’m not really clear how the same ATLAS can generate two PLEs (which are extremely similar in design) and one exhibits some kind of SQLRender issue and the other does not.
The ErrorReport.txt tells me this:
DBMS:
redshiftError:
java.sql.SQLException: Amazon Invalid operation: syntax error at or near “)”
Position: 699;SQL:
CREATE TABLE #qualified_eventsDISTKEY(person_id)
AS
WITH
primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id)
AS
(
– Begin Primary Events
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 bigint) 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 ) 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 bigint) as visit_occurrence_id
FROM
() E
JOIN full_201907_omop_v5.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 DATEADD(day,CAST(0 as int),OP.OBSERVATION_PERIOD_START_DATE) <= E.START_DATE AND DATEADD(day,CAST(0 as int),E.START_DATE) <= OP.OBSERVATION_PERIOD_END_DATE
) P
WHERE P.ordinal = 1
– End Primary Events)
SELECT
event_id, person_id , start_date, end_date, op_start_date, op_end_date, visit_occurrence_idFROM
(
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 bigint) as visit_occurrence_id
FROM primary_events pe) QE
R version:
R version 3.5.1 (2018-07-02)Platform:
x86_64-pc-linux-gnuAttached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- stringr (1.4.0)
- plyr (1.8.4)
- survival (2.44-1.1)
- ParallelLogger (1.1.0)
- OhdsiSharing (0.1.3)
- MethodEvaluation (1.0.1)
- ffbase (0.12.7)
- ff (2.2-14)
- bit (1.1-14)
- ggplot2 (3.2.1)
- CohortMethod (3.0.2)
- FeatureExtraction (2.2.3)
- Cyclops (2.0.2)
- EmpiricalCalibration (2.0.0)
- SqlRender (1.6.3)
- devtools (2.2.1)
- usethis (1.5.1)
- BridgetTofa1108 (0.0.1)
- DatabaseConnector (2.4.1)
Guess it goes to show to trust but verify. Case closed.