Cohort generation status failed

[blasisd (Vlasios Dimitriadis)]

Hello everybody,

I am having a problem generating cohorts. Every time I push the generate button I get a “Failed” generation status with the following error message:

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_
)
; INSERT INTO Codesets (codeset_id, concept_id)
SELECT 0 as codeset_id, c.concept_id FROM (select distinct I.concept_id FROM
( _
_ select concept_id from cdm.CONCEPT where concept_id in (21601857,21601900)

) I
) C; 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_dat… [truncated] …ect COUNT(event_id) as total from best_events) PC,_
(select sum(sr.person_count) as total
_ from ohdsi.cohort_inclusion_result sr_
_ CROSS JOIN (select count(*) as total_rules from ohdsi.cohort_inclusion where cohort_definition_id = 25) RuleTotal_
_ where sr.mode_id = 1 and sr.cohort_definition_id = 25 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 15 delete from ohdsi.cohort_inclusion_result where cohort_definition_id = 25 and mode_id = 0 was aborted. Call getNextException to see the cause.

I am using Broadsea docker container ( ATLAS Version 2.7.2, WebAPI Version 2.7.2 and postgresql)

I was wondering whether someone could help.
Thanks in advance!

[blasisd (Vlasios Dimitriadis)]

For some reason, the scripts that I had run, had created some tables in the results schema, with missing mode_id field and wrong table constraints. After running:

ALTER TABLE ohdsi.cohort_inclusion_result ADD mode_id int NOT NULL DEFAULT 0;
ALTER TABLE ohdsi.cohort_inclusion_stats ADD mode_id int NOT NULL DEFAULT** 0;
ALTER TABLE ohdsi.cohort_summary_stats ADD mode_id int NOT NULL DEFAULT 0;

The following post helped me a lot in the debugging process:

Cohort Generation in Atlas with Generation status Failed

Hi All, I am also facing a similar issue : I am currently analyzing the features offered by the ATLAS tool. We are using a sample SynPuf data for this activity. We are using PostgreSQL based AWS RDS as the database. Observation : The ATLAS tool created the cohorts and the reports were generated successfully. However, when we went to perform characterization and build cohort pathways, the process failed with the below error: java.util.concurrent.CompletionException: org.springframework.jdbc.B…

and removing pkey constraint from tables cohort_inclusion_result, cohort_inclusion_stats, cohort_inclusion and cohort_summary_stats everything seems to be working fine.