OHDSI Home | Forums | Wiki | Github

Solved: PLE Package Error - Redshift Syntax for CreateAllCohorts at NegativeControls


(Kristin Kostka, MPH) #1

@schuemie,

Getting an error inside of a freshly minted PLE package from Atlas around the Redshift syntax for CreateAllCohorts at Line 73.

Error looks like this:

DBMS:
redshift

Error:
java.sql.SQLException: Amazon Invalid operation: syntax error at or near “)”
Position: 182;

SQL:
INSERT INTO #Codesets (ancestor_concept_id, concept_id)
SELECT ancestor_concept_id, descendant_concept_id
FROM full_201907_omop_v5.CONCEPT_ANCESTOR
WHERE ancestor_concept_id IN ()

My traceback is:
R version:
R version 3.5.1 (2018-07-02)

Platform:
x86_64-pc-linux-gnu

Attached 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)
  • TOFA20200128 (0.0.1)
  • DatabaseConnector (2.4.1)

It looks like the package is upset with syntax around this point:

DatabaseConnector::executeSql(conn, sql)

Coming out of:

sql <- SqlRender::loadRenderTranslateSql(“NegativeControlOutcomes.sql”,
“TOFA20200128”,
dbms = connectionDetails$dbms,
oracleTempSchema = oracleTempSchema,
cdm_database_schema = cdmDatabaseSchema,
target_database_schema = cohortDatabaseSchema,
target_cohort_table = cohortTable,
outcome_ids = unique(negativeControlOutcomes$outcomeId))

According to GitHub, it’s been about 28 days since I’ve tried to run a full PLE so wondering what’s new and different. I think my packages are up to date but maybe that’s my problem. Is there something I’m missing re: SqlRender? I’ve only seen this kind of syntax issue when I was working with @cukarthik’s custom code. Usually this doesn’t come up in an out of Atlas PLE.


(Martijn Schuemie) #2

Looks like there are no negative control outcome IDs?

Did you define negative controls? Could you check inst/settings/NegativeControls.csv to see if it is empty?


(Kristin Kostka, MPH) #3

Doh. :woman_facepalming:t3:

You’re right. Looks like the Negative Controls were inadvertently put into the Covariates to Include selection in the Atlas Estimation settings.

I’ll show myself out.


t