OHDSI Home | Forums | Wiki | Github

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

@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.

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?

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