ETL-CDMBuilder INPATIENT_SERVICES table issue

dantohe · August 2, 2018, 8:03pm

The builder is expecting a column (dx5) that doesn’t seem to exists in Truven CCAE (2015) data.

OHDSI/ETL-CDMBuilder/blob/master/source/Builders/org.ohdsi.cdm.builders.truven_v5/TruvenV5/Definitions/INPATIENT_SERVICES.xml

<QueryDefinition>
  <Variables>
    <Variable name="providField" database="ccae, mdcr">CAST(PROVID AS VARCHAR)</Variable>
    <Variable name="providField" database="mdcd">PROV_ID</Variable>

    <Variable name="procmod" database="ccae, mdcr">PROCMOD</Variable>
    <Variable name="procmod" database="mdcd">NULL AS PROCMOD</Variable>
    
    <Variable name="diagnosisField" database="ccae, mdcr">PDX, DX1, DX2, DX3, DX4, DX5</Variable>
    <Variable name="diagnosisField" database="mdcd">PDX, DX1, DX2, DX3, DX4, null DX5</Variable>
  </Variables>
  <Query>

    with a as (
    select {diagnosisField}, PPROC, PROC1, SVCDATE, ENROLID,
    ADMDATE, STDPLAC,
    STDPROV,
    DISDATE, COPAY, COINS, CASEID, TSVCDAT,
    CASE
    WHEN (

This file has been truncated. show original

Error from Source: Amazon Redshift ODBC Driver: Message: ERROR [42703] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42703] ERROR: column “dx5” does not exist in inpatient_services, ch, unnamed_join

The following columns are part of these data (taken from the Truven sample data):
SEQNUM VERSION DX1 DX2 DXVER PROC1 PROCTYP CASEID DISDATE DOBYR YEAR ADMDATE AGE CAP_SVC COB COINS COPAY DEDUCT DRG DX3 DX4 FACHDID FACPROF MHSACOVG MSCLMID NETPAY NPI NTWKPROV PAIDNTWK PAY PDDATE PDX PPROC PROCMOD PROVID QTY REVCODE SVCDATE SVCSCAT TSVCDAT UNITS ADMTYP MDC DSTATUS STDPLAC STDPROV EFAMID ENROLID PLANTYP REGION MSA DATATYP AGEGRP EECLASS EESTATU EGEOLOC EIDFLAG EMPREL ENRFLAG PHYFLAG RX SEX HLTHPLAN INDSTRY

Any advice on a work around will be appreciated.

Thanks

dantohe · August 3, 2018, 2:10pm

case followup in github: