Is it a bug in the ETLSyntheaBuider insert_location.sql(5.4 5.3)?

[baiyuqi]

I always get an error saying “cdm_synthea_30_v54.states_map” does not exist when invoking the LoadEventTables
And I find the “cdm_synthea_30_v54” in the insert_location.sql.
Is it a bug? Can’t guess any reason to suppose this schema exists in database.
I change it to “@cdmSchema” and the LoadEventTables seem to work fine.
Can anybody tell?

[burrowse]

Thank you for bringing this to our attention @baiyuqi! I have a applied a patch. Please let us know if you have any issues.

[mpreusse]

I still can't load when I install from GitHub with devtools::install_github("OHDSI/ETL-Synthea") which should always get the latest commit from the main branch.

Here is the error message:

Inserting data took 0.0899 secs
Loading: supplies.csv
Inserting data took 0.00412 secs
Connecting using PostgreSQL driver
Running: insert_location.sql
Error in `.createErrorReport()`:
! Error executing SQL:
org.postgresql.util.PSQLException: ERROR: relation "synthea.states_map" does not exist
  Position: 550
An error report has been created at  //errorReportSql.txt
Backtrace:
    ▆
 1. └─ETLSyntheaBuilder::LoadEventTables(...)
 2.   └─ETLSyntheaBuilder (local) runStep(sql, fileQuery)
 3.     └─DatabaseConnector::executeSql(conn, sql)
 4.       └─base::tryCatch(...)
 5.         └─base (local) tryCatchList(expr, classes, parentenv, handlers)
 6.           └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
 7.             └─value[[3L]](cond)
 8.               └─DatabaseConnector:::.createErrorReport(dbms, err$message, sqlStatement, errorReportFile)
 9.                 └─rlang::abort(...)
Execution halted

[mpreusse]

It's related to this SQL:

left join @cdm_schema.states_map states_map on p.state=states_map.state) locations

I think this: https://github.com/OHDSI/ETL-Synthea/blob/main/inst/sql/sql_server/cdm_version/v531/create_states_map.sql is not executed before insert_location.sql

[mpreusse]

The issue was that I didn't execute ETLSyntheaBuilder::CreateMapAndRollupTables, sorry 😄

[burrowse]

No problem @mpreusse! That's a new step we've introduced with the most recent update. Thanks for giving the new version of the package a try :)