[PatientLevelPrediction] dbSendUpdate error

Implementers
1

Hi I’m implementing the PatientLevelPrediction package and some of the sql commands encountered an error while running. I believe the error is due to my misunderstanding of how the result-cohort is supposed to be set up.
For example: I encountered the following error as getDbPlpData() was called and subsequently dbSendUpdate() caused the error. I am not sure where exactly the cohor_definition_id column is supposed to be set up. But overall I want to check that I have my database set up correctly before I attempt to run again. Could someone guide me through the process of setting up the correct cohorts/schemas in the database?

BMS:
postgresql

Error:
execute JDBC update query failed in dbSendUpdate (ERROR: column "cohort_definition_id"    does not exist
Position: 216)

SQL:
CREATE TEMP TABLE cohort_person

AS
SELECT
ROW_NUMBER() OVER (ORDER BY subject_id, cohort_start_date) AS row_id,
    cohort_definition_id,
    subject_id,
    cohort_start_date,
    cohort_end_date

FROM
(
    SELECT DISTINCT cohort_definition_id,
            subject_id,
            cohort_start_date,

            CASE
                    WHEN ( cohort_end_date +  0) > observation_period_end_date THEN observation_period_end_date
                    ELSE ( cohort_end_date +  0)
            END AS cohort_end_date

    FROM public.rehospitalization cohort
    INNER JOIN observation_period
    ON cohort.subject_id = observation_period.person_id
    WHERE cohort_start_date >= observation_period_start_date
    AND cohort_start_date <= observation_period_end_date

    AND cohort_definition_id IN (1)

    AND cohort_start_date >= ( observation_period_start_date +  183)
) unique_periods


R version:
R version 3.2.3 (2015-12-10)

Platform:
x86_64-redhat-linux-gnu

Attached base packages:
- methods
- stats
graphics
- grDevices
- utils
- datasets
- base

Other attached packages:
- SqlRender (1.1.3)
- PatientLevelPrediction (1.1.0)
- Cyclops (1.2.0)
- DatabaseConnector (1.3.0)
- RJDBC (0.2-5)
- rJava (0.9-8)
- DBI (0.3.1)
2

I believe there are a couple of ways to create a cohort. The end results are entries in the public.cohort table. cohort_definition_id, subject_id ( person_id), cohort_start date, cohort_end date. There are also entries made in the public.cohort_definition and public.cohort_definition_details tables. These are probably necessary for DB constraint issues, but I am not sure if they are needed otherwise if you are hacking around it. The cohort_definition_details contains the JSON string describing the cohort creation.

I have used CIRCE/HERMES to create cohorts, but it probably wouldn’t be very difficult to do it manually. Circe has the option to show you the sql code used to create the cohort and that is useful to see what it is doing. Circe is your best bet if you have WebAPI set up.

Hope this helps.

3

From Martijn Schuemie, one of the authors of the package:

Hi Jefferson,

The package is looking for a field named ‘cohort_definition_id’ in your cohort table (‘rehospitalization’), but can’t find it.

Note that one difference between OMOP CDM V4 and V5 is that this identifier was renamed from ‘cohort_concept_id’ to ‘cohort_definition_id’ (because it’s not a concept ID). For the user-supplied cohort table for the PLP package we decided to follow the same convention: if you’re using CDM V4, you should use the old name, for V5 you should use the new name. The example in the vignette assumes you’re running CDM V4, but I’m guessing you’re using V5 instead (I’ve written somewhere that you should change that, but I’m not surprised you didn’t find it).

Cheers,
Martijn