OHDSI Home | Forums | Wiki | Github

Cohort generation failed - invalid column mode_id

(Malcolm McRoberts) #1

I’m running ATLAS 2.6.0 and WedAPI 2.6.0 against with SynPUF data in SQL Server 2016 loaded from the backup provided in the quick start.

Cohort generation is failing do to references to column mode_id . Is this a version mismatch? Any suggestions? Here’s a snippet from the logs:

(select 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 = 1) RuleTotal
where sr.mode_id = 1 and sr.cohort_definition_id = 1 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: Invalid column name ‘mode_id’.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:600)
at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.lambda$doTask$132(GenerateCohortTasklet.java:141)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
… 3 more
Caused by: java.sql.BatchUpdateException: Invalid column name ‘mode_id’.
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeBatch(SQLServerStatement.java:1740)
at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:561)
at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:548)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
… 6 more

(Chris Knoll) #2

This was a new column added in the 2.5 release described here:

You can execute the following steps (making any adjustments you need for your environment):

ALTER TABLE @resultsSchema.cohort_inclusion_result ADD mode_id  int NOT NULL DEFAULT 0;
ALTER TABLE @resultsSchema.cohort_inclusion_stats ADD mode_id  int NOT NULL DEFAULT 0;
ALTER TABLE @resultsSchema.cohort_summary_stats ADD mode_id  int NOT NULL DEFAULT 0;

(Malcolm McRoberts) #3

Thanks, that was very helpful. That got me past the errors I was seeing. Cohort generation now completes, but the cohort I generated is empty.

What code creates these tables? I thought I was 2.6.0 to start with (not upgrading from before 2.5). Could the R code be out of sync? Is there anything I need to do after the alter table command? Looks like there should be some patients in the cohort. Trying to figure out if it’s my cohort definition, or an atlas problem. I’m pretty new to this. The cohort is supposed to be breast cancer patients who have had abraxane. I’ve found concepts that have plenty of patients in the SynPUF100K data set, but the cohort is empty.

(Chris Knoll) #4

Atlas allows you to see the cohort generation SQL under the Export -> SQL tab. You can run it in pieces to see where you start getting zero records.

I’m not sure what you mean by “Could the R code be out of syinc?” We haven’t been talking about R code have we?