OHDSI Home | Forums | Wiki | Github

Cohort Generation Error

Developers
#1

I get the following error when running Cohort generation in Atlas:

java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TEMP TABLE Codesets (codeset_id int NOT NULL,
concept_id bigint NOT NULL
)
; CREATE TEMP TABLE qualified_events

AS
WITH primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id) AS (
– Begin Primary Events
select P.ordinal as event_id, P.person_id, P.start_date, P.end_date, op_start_date, op_end_date, cast(P.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
select E.person_id, E.start_date, E.end_date, row_number() OVER (PARTITION BY E.person_id ORDER BY E.start_date ASC) ordinal, OP.observation_period_start_date as op_start_date, OP.observation_period_end_date as op_end_date, cast(E.visit_occurrence_id as bigint) as visit_occurrence_id
FROM
(
– Begin Condition Occurrence Criteria
SELECT C.person_id, C.condition_occurrence_id as e… [truncated] …ect COUNT(event_id) as total from best_events) PC,
(select sum(sr.person_count) as total
from public.cohort_inclusion_result sr
CROSS JOIN (select count(*) as total_rules from public.cohort_inclusion where cohort_definition_id = 2) RuleTotal
where sr.mode_id = 1 and sr.cohort_definition_id = 2 and sr.inclusion_rule_mask = POWER(cast(2 as bigint),RuleTotal.total_rules)-1
) FC
; TRUNCATE TABLE best_events; DROP TABLE best_events; TRUNCATE TABLE cohort_rows; DROP TABLE cohort_rows; TRUNCATE TABLE final_cohort; DROP TABLE final_cohort; TRUNCATE TABLE inclusion_events; DROP TABLE inclusion_events; TRUNCATE TABLE qualified_events; DROP TABLE qualified_events; TRUNCATE TABLE included_events; DROP TABLE included_events; TRUNCATE TABLE Codesets; DROP TABLE Codesets]; nested exception is java.sql.BatchUpdateException: Batch entry 14 delete from public.cohort_inclusion_result where cohort_definition_id = 2 and mode_id = 0 was aborted. Call getNextException to see the cause.

I’m running PostgreSQL 12.2. Has anyone else seen this error before?

#2 
[{"sourceId":1,"sourceName":"OHDSI CDM V5 Database","sourceDialect":"postgresql","sourceKey":"OHDSI-CDMV5","daimons":[{"sourceDaimonId":1,"daimonType":"CDM","tableQualifier":"public","priority":2},{"sourceDaimonId":2,"daimonType":"Vocabulary","tableQualifier":"public","priority":2},{"sourceDaimonId":3,"daimonType":"Results","tableQualifier":"ohdsi","priority":2},{"sourceDaimonId":4,"daimonType":"Temp","tableQualifier":"temp","priority":0}]}]
#3

I don’t know exactly what the error was, but the DELETE statement above looks like it failed.

Did you create the results schema in your CDM database? It looks like you configured it to be in the ‘ohdsi’ schema.

#4

That was the step that I was missing. I expected the application to automatically create the results schema. I also needed to keep public, ohdsi and results schemas separate to make it work properly.

1 Like
t