I’m a version 2.5 Atlas Developer in oracle(same WebAPI version 2.5).
When I generate cohort I face something strange error
###########################################################
_`2019-02-01 17:05:41.032 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.JpaTransactionManager - - Initiating transaction commit_
_ 2019-02-01 17:05:41.032 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.JpaTransactionManager - - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@7e27c1c]_
_ 2019-02-01 17:05:41.033 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.JpaTransactionManager - - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@7e27c1c] after transaction_
_ 2019-02-01 17:05:41.033 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager_
_ 2019-02-01 17:05:41.761 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DataSourceUtils - - Returning JDBC Connection to DataSource_
_ 2019-02-01 17:05:41.763 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodesFactory - - Looking up default SQLErrorCodes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@3375338e]_
_ 2019-02-01 17:05:41.763 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DataSourceUtils - - Fetching JDBC Connection from DataSource_
_ 2019-02-01 17:05:41.763 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DriverManagerDataSource - - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:RESULT_CDM_2018/cdm_2018!dmlfywjdqh@172.28.40.243:1521/SNUHOS]_
_ 2019-02-01 17:05:41.796 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DataSourceUtils - - Returning JDBC Connection to DataSource_
_ 2019-02-01 17:05:41.797 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodesFactory - - SQL error codes for 'Oracle' found_
_ 2019-02-01 17:05:41.797 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodesFactory - - Caching SQL error codes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@3375338e]: database product name is 'Oracle'_
_ 2019-02-01 17:05:41.797 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - - Unable to translate SQLException with Error code '17081', will now try the fallback translator_
_ 2019-02-01 17:05:41.835 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Participating transaction failed - marking existing transaction as rollback-only_
_ 2019-02-01 17:05:41.835 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Setting JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@3f01d09d] rollback-only_
_ 2019-02-01 17:05:41.835 DEBUG taskExecutor-9 org.springframework.jdbc.datasource.DataSourceUtils - - Returning JDBC Connection to DataSource_
_ 2019-02-01 17:05:41.836 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Initiating transaction rollback_
_ 2019-02-01 17:05:41.836 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Rolling back JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@3f01d09d]_
_ 2019-02-01 17:05:41.837 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@3f01d09d] after transaction_
_ 2019-02-01 17:05:41.837 DEBUG taskExecutor-9 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager_
_ 2019-02-01 17:05:41.840 ERROR taskExecutor-9 org.springframework.batch.core.step.AbstractStep - - Encountered an error executing step cohortDefinition.generateCohort in job generateCohort_
_ java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE q8i4abb0Codesets (_
_ codeset_id int NOT NULL,_
_ concept_id NUMBER(19) NOT NULL_
_ )_
_ ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 0 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (4325006)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (4325006)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; _
_ ._
_ ._
_ ._
_ delete from RESULT_CDM_2018.cohort_inclusion_stats where cohort_definition_id = 1996 and mode_id = 1; insert into RESULT_CDM_2018.cohort_inclusion_stats (cohort_definition_id, rule_sequence, person_count, gain_count, person_total, mode_id)_
_ SELECT ir.cohort_definition_id, ir.rule_sequence, coalesce(T.person_count, 0) as person_count, coalesce(SR.person_count, 0) gain_count, EventTotal.total, 1 as mode_id_
_ FROM RESULT_CDM_2018.cohort_inclusion ir_
_ left join_
_ (SELECT i.inclusion_rule_id, count(i.event_id) as person_count_
_ FROM q8i4abb0best_events Q_
_ JOIN q8i4abb0inclusion_events i on Q.person_id = I.person_id and Q.event_id = i.event_id_
_ group by i.inclusion_rule_id_
_ ) T on ir.rule_sequence = T.inclusion_rule_id_
_ CROSS JOIN (SELECT count(*) as total_rules FROM RESULT_CDM_2018.cohort_inclusion WHERE cohort_definition_id = 1996 ) RuleTotal_
_ CROSS JOIN (SELECT count(event_id) as total FROM q8i4abb0best_events ) EventTotal_
_ LEFT JOIN RESULT_CDM_2018.cohort_inclusion_result SR on SR.mode_id = 1 AND SR.cohort_definition_id = 1996 AND (POWER(cast(2 as NUMBER(19)),RuleTotal.total_rules) - POWER(cast(2 as NUMBER(19)),ir.rule_sequence) - 1) = SR.inclusion_rule_mask -- POWER(2,rule count) - POWER(2,rule sequence) - 1 is the mask for 'all except this rule' _
_ WHERE ir.cohort_definition_id = 1996_
_ ; delete from RESULT_CDM_2018.cohort_summary_stats where cohort_definition_id = 1996 and mode_id = 1; insert into RESULT_CDM_2018.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)_
_ SELECT 1996 as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 1 as mode_id_
_ FROM (SELECT count(event_id) as total FROM q8i4abb0best_events ) PC,_
_ (SELECT sum(sr.person_count) as total_
_ FROM RESULT_CDM_2018.cohort_inclusion_result sr_
_ CROSS JOIN (SELECT count(*) as total_rules FROM RESULT_CDM_2018.cohort_inclusion WHERE cohort_definition_id = 1996 ) RuleTotal_
_ WHERE sr.mode_id = 1 and sr.cohort_definition_id = 1996 and sr.inclusion_rule_mask = POWER(cast(2 as NUMBER(19)),RuleTotal.total_rules)-1_
_ ) FC_
_ ; TRUNCATE TABLE q8i4abb0best_events; DROP TABLE q8i4abb0best_events; TRUNCATE TABLE q8i4abb0cohort_rows; DROP TABLE q8i4abb0cohort_rows; TRUNCATE TABLE q8i4abb0final_cohort; DROP TABLE q8i4abb0final_cohort; TRUNCATE TABLE q8i4abb0inclusion_events; DROP TABLE q8i4abb0inclusion_events; TRUNCATE TABLE q8i4abb0qualified_events; DROP TABLE q8i4abb0qualified_events; TRUNCATE TABLE q8i4abb0included_events; DROP TABLE q8i4abb0included_events; TRUNCATE TABLE q8i4abb0Codesets; DROP TABLE q8i4abb0Codesets]; SQL state [null]; error code [17081]; 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ ; nested exception is java.sql.BatchUpdateException: 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.doTask(GenerateCohortTasklet.java:156)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.access$000(GenerateCohortTasklet.java:56)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet$1.doInTransaction(GenerateCohortTasklet.java:168)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet$1.doInTransaction(GenerateCohortTasklet.java:165)_
_ at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.execute(GenerateCohortTasklet.java:165)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)_
_ at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)_
_ at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)_
_ at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)_
_ at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)_
_ at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)_
_ at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)_
_ at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)_
_ at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392)_
_ at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135)_
_ at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)_
_ at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)_
_ at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)_
_ at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)_
_ at java.lang.Thread.run(Thread.java:748)_
_ Caused by: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE q8i4abb0Codesets (_
_ codeset_id int NOT NULL,_
_ concept_id NUMBER(19) NOT NULL_
_ )_
_ ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 0 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (4325006)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (4325006)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 1 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (21600095)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (21600095)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; _
_ ._
_ ._
_ ._
_ ; delete from RESULT_CDM_2018.cohort_summary_stats where cohort_definition_id = 1996 and mode_id = 1; insert into RESULT_CDM_2018.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)_
_ SELECT 1996 as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 1 as mode_id_
_ FROM (SELECT count(event_id) as total FROM q8i4abb0best_events ) PC,_
_ (SELECT sum(sr.person_count) as total_
_ FROM RESULT_CDM_2018.cohort_inclusion_result sr_
_ CROSS JOIN (SELECT count(*) as total_rules FROM RESULT_CDM_2018.cohort_inclusion WHERE cohort_definition_id = 1996 ) RuleTotal_
_ WHERE sr.mode_id = 1 and sr.cohort_definition_id = 1996 and sr.inclusion_rule_mask = POWER(cast(2 as NUMBER(19)),RuleTotal.total_rules)-1_
_ ) FC_
_ ; TRUNCATE TABLE q8i4abb0best_events; DROP TABLE q8i4abb0best_events; TRUNCATE TABLE q8i4abb0cohort_rows; DROP TABLE q8i4abb0cohort_rows; TRUNCATE TABLE q8i4abb0final_cohort; DROP TABLE q8i4abb0final_cohort; TRUNCATE TABLE q8i4abb0inclusion_events; DROP TABLE q8i4abb0inclusion_events; TRUNCATE TABLE q8i4abb0qualified_events; DROP TABLE q8i4abb0qualified_events; TRUNCATE TABLE q8i4abb0included_events; DROP TABLE q8i4abb0included_events; TRUNCATE TABLE q8i4abb0Codesets; DROP TABLE q8i4abb0Codesets]; SQL state [null]; error code [17081]; 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ ; nested exception is java.sql.BatchUpdateException: 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ at java.util.concurrent.FutureTask.report(FutureTask.java:122)_
_ at java.util.concurrent.FutureTask.get(FutureTask.java:192)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.doTask(GenerateCohortTasklet.java:146)_
_ ... 23 more_
_ Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE q8i4abb0Codesets (_
_ codeset_id int NOT NULL,_
_ concept_id NUMBER(19) NOT NULL_
_ )_
_ ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 0 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (4325006)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (4325006)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 1 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (21600095)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (21600095)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ;_
_ ._
_ ._
_ ._
_ 2019-02-01 17:05:41.919 DEBUG taskExecutor-9 org.springframework.jdbc.core.JdbcTemplate - - Executing prepared SQL statement [SELECT VERSION FROM BESTCARE_RESULT.BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID=?]_
_ 2019-02-01 17:05:41.920 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Initiating transaction commit_
_ 2019-02-01 17:10:06.193 DEBUG task-scheduler-6 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager` _
_ [quote="11112, post:1, topic:6032, full:true"]_
_ I'm a version 2.5 Atlas Developer in oracle(same WebAPI version 2.5)._
_ When I generate cohort I face something strange error_
_ `2019-02-01 17:05:41.032 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.JpaTransactionManager - - Initiating transaction commit_
_ 2019-02-01 17:05:41.032 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.JpaTransactionManager - - Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@7e27c1c]_
_ 2019-02-01 17:05:41.033 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.JpaTransactionManager - - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@7e27c1c] after transaction_
_ 2019-02-01 17:05:41.033 DEBUG http-nio-18080-exec-4 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager_
_ 2019-02-01 17:05:41.761 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DataSourceUtils - - Returning JDBC Connection to DataSource_
_ 2019-02-01 17:05:41.763 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodesFactory - - Looking up default SQLErrorCodes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@3375338e]_
_ 2019-02-01 17:05:41.763 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DataSourceUtils - - Fetching JDBC Connection from DataSource_
_ 2019-02-01 17:05:41.763 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DriverManagerDataSource - - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:RESULT_CDM_2018/cdm_2018!dmlfywjdqh@172.28.40.243:1521/SNUHOS]_
_ 2019-02-01 17:05:41.796 DEBUG pool-10-thread-1 org.springframework.jdbc.datasource.DataSourceUtils - - Returning JDBC Connection to DataSource_
_ 2019-02-01 17:05:41.797 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodesFactory - - SQL error codes for 'Oracle' found_
_ 2019-02-01 17:05:41.797 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodesFactory - - Caching SQL error codes for DataSource [org.springframework.jdbc.datasource.DriverManagerDataSource@3375338e]: database product name is 'Oracle'_
_ 2019-02-01 17:05:41.797 DEBUG pool-10-thread-1 org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - - Unable to translate SQLException with Error code '17081', will now try the fallback translator_
_ 2019-02-01 17:05:41.835 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Participating transaction failed - marking existing transaction as rollback-only_
_ 2019-02-01 17:05:41.835 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Setting JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@3f01d09d] rollback-only_
_ 2019-02-01 17:05:41.835 DEBUG taskExecutor-9 org.springframework.jdbc.datasource.DataSourceUtils - - Returning JDBC Connection to DataSource_
_ 2019-02-01 17:05:41.836 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Initiating transaction rollback_
_ 2019-02-01 17:05:41.836 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Rolling back JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@3f01d09d]_
_ 2019-02-01 17:05:41.837 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@3f01d09d] after transaction_
_ 2019-02-01 17:05:41.837 DEBUG taskExecutor-9 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager_
_ 2019-02-01 17:05:41.840 ERROR taskExecutor-9 org.springframework.batch.core.step.AbstractStep - - Encountered an error executing step cohortDefinition.generateCohort in job generateCohort_
_ java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE q8i4abb0Codesets (_
_ codeset_id int NOT NULL,_
_ concept_id NUMBER(19) NOT NULL_
_ )_
_ ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 0 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (4325006)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (4325006)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; _
_ ._
_ ._
_ ._
_ delete from RESULT_CDM_2018.cohort_inclusion_stats where cohort_definition_id = 1996 and mode_id = 1; insert into RESULT_CDM_2018.cohort_inclusion_stats (cohort_definition_id, rule_sequence, person_count, gain_count, person_total, mode_id)_
_ SELECT ir.cohort_definition_id, ir.rule_sequence, coalesce(T.person_count, 0) as person_count, coalesce(SR.person_count, 0) gain_count, EventTotal.total, 1 as mode_id_
_ FROM RESULT_CDM_2018.cohort_inclusion ir_
_ left join_
_ (SELECT i.inclusion_rule_id, count(i.event_id) as person_count_
_ FROM q8i4abb0best_events Q_
_ JOIN q8i4abb0inclusion_events i on Q.person_id = I.person_id and Q.event_id = i.event_id_
_ group by i.inclusion_rule_id_
_ ) T on ir.rule_sequence = T.inclusion_rule_id_
_ CROSS JOIN (SELECT count(*) as total_rules FROM RESULT_CDM_2018.cohort_inclusion WHERE cohort_definition_id = 1996 ) RuleTotal_
_ CROSS JOIN (SELECT count(event_id) as total FROM q8i4abb0best_events ) EventTotal_
_ LEFT JOIN RESULT_CDM_2018.cohort_inclusion_result SR on SR.mode_id = 1 AND SR.cohort_definition_id = 1996 AND (POWER(cast(2 as NUMBER(19)),RuleTotal.total_rules) - POWER(cast(2 as NUMBER(19)),ir.rule_sequence) - 1) = SR.inclusion_rule_mask -- POWER(2,rule count) - POWER(2,rule sequence) - 1 is the mask for 'all except this rule' _
_ WHERE ir.cohort_definition_id = 1996_
_ ; delete from RESULT_CDM_2018.cohort_summary_stats where cohort_definition_id = 1996 and mode_id = 1; insert into RESULT_CDM_2018.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)_
_ SELECT 1996 as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 1 as mode_id_
_ FROM (SELECT count(event_id) as total FROM q8i4abb0best_events ) PC,_
_ (SELECT sum(sr.person_count) as total_
_ FROM RESULT_CDM_2018.cohort_inclusion_result sr_
_ CROSS JOIN (SELECT count(*) as total_rules FROM RESULT_CDM_2018.cohort_inclusion WHERE cohort_definition_id = 1996 ) RuleTotal_
_ WHERE sr.mode_id = 1 and sr.cohort_definition_id = 1996 and sr.inclusion_rule_mask = POWER(cast(2 as NUMBER(19)),RuleTotal.total_rules)-1_
_ ) FC_
_ ; TRUNCATE TABLE q8i4abb0best_events; DROP TABLE q8i4abb0best_events; TRUNCATE TABLE q8i4abb0cohort_rows; DROP TABLE q8i4abb0cohort_rows; TRUNCATE TABLE q8i4abb0final_cohort; DROP TABLE q8i4abb0final_cohort; TRUNCATE TABLE q8i4abb0inclusion_events; DROP TABLE q8i4abb0inclusion_events; TRUNCATE TABLE q8i4abb0qualified_events; DROP TABLE q8i4abb0qualified_events; TRUNCATE TABLE q8i4abb0included_events; DROP TABLE q8i4abb0included_events; TRUNCATE TABLE q8i4abb0Codesets; DROP TABLE q8i4abb0Codesets]; SQL state [null]; error code [17081]; 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ ; nested exception is java.sql.BatchUpdateException: 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.doTask(GenerateCohortTasklet.java:156)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.access$000(GenerateCohortTasklet.java:56)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet$1.doInTransaction(GenerateCohortTasklet.java:168)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet$1.doInTransaction(GenerateCohortTasklet.java:165)_
_ at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.execute(GenerateCohortTasklet.java:165)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)_
_ at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:272)_
_ at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)_
_ at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)_
_ at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)_
_ at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)_
_ at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)_
_ at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)_
_ at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)_
_ at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:392)_
_ at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135)_
_ at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)_
_ at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)_
_ at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)_
_ at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)_
_ at java.lang.Thread.run(Thread.java:748)_
_ Caused by: java.util.concurrent.ExecutionException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE q8i4abb0Codesets (_
_ codeset_id int NOT NULL,_
_ concept_id NUMBER(19) NOT NULL_
_ )_
_ ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 0 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (4325006)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (4325006)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 1 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (21600095)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (21600095)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; _
_ ._
_ ._
_ ._
_ ; delete from RESULT_CDM_2018.cohort_summary_stats where cohort_definition_id = 1996 and mode_id = 1; insert into RESULT_CDM_2018.cohort_summary_stats (cohort_definition_id, base_count, final_count, mode_id)_
_ SELECT 1996 as cohort_definition_id, PC.total as person_count, coalesce(FC.total, 0) as final_count, 1 as mode_id_
_ FROM (SELECT count(event_id) as total FROM q8i4abb0best_events ) PC,_
_ (SELECT sum(sr.person_count) as total_
_ FROM RESULT_CDM_2018.cohort_inclusion_result sr_
_ CROSS JOIN (SELECT count(*) as total_rules FROM RESULT_CDM_2018.cohort_inclusion WHERE cohort_definition_id = 1996 ) RuleTotal_
_ WHERE sr.mode_id = 1 and sr.cohort_definition_id = 1996 and sr.inclusion_rule_mask = POWER(cast(2 as NUMBER(19)),RuleTotal.total_rules)-1_
_ ) FC_
_ ; TRUNCATE TABLE q8i4abb0best_events; DROP TABLE q8i4abb0best_events; TRUNCATE TABLE q8i4abb0cohort_rows; DROP TABLE q8i4abb0cohort_rows; TRUNCATE TABLE q8i4abb0final_cohort; DROP TABLE q8i4abb0final_cohort; TRUNCATE TABLE q8i4abb0inclusion_events; DROP TABLE q8i4abb0inclusion_events; TRUNCATE TABLE q8i4abb0qualified_events; DROP TABLE q8i4abb0qualified_events; TRUNCATE TABLE q8i4abb0included_events; DROP TABLE q8i4abb0included_events; TRUNCATE TABLE q8i4abb0Codesets; DROP TABLE q8i4abb0Codesets]; SQL state [null]; error code [17081]; 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ ; nested exception is java.sql.BatchUpdateException: 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_
_ at java.util.concurrent.FutureTask.report(FutureTask.java:122)_
_ at java.util.concurrent.FutureTask.get(FutureTask.java:192)_
_ at org.ohdsi.webapi.cohortdefinition.GenerateCohortTasklet.doTask(GenerateCohortTasklet.java:146)_
_ ... 23 more_
_ Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE q8i4abb0Codesets (_
_ codeset_id int NOT NULL,_
_ concept_id NUMBER(19) NOT NULL_
_ )_
_ ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 0 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (4325006)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (4325006)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ; INSERT INTO q8i4abb0Codesets (codeset_id, concept_id)_
_ SELECT 1 as codeset_id, c.concept_id FROM (SELECT distinct I.concept_id FROM (SELECT concept_id FROM CDM_VOCA.CONCEPT WHERE concept_id in (21600095)and invalid_reason is null_
_ UNION select c.concept_id_
_ FROM CDM_VOCA.CONCEPT c_
_ join CDM_VOCA.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id_
_ and ca.ancestor_concept_id in (21600095)_
_ and c.invalid_reason is null_
_ ) I_
_ ) C ;_
_ ._
_ ._
_ ._
_ 2019-02-01 17:05:41.919 DEBUG taskExecutor-9 org.springframework.jdbc.core.JdbcTemplate - - Executing prepared SQL statement [SELECT VERSION FROM BESTCARE_RESULT.BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID=?]_
_ 2019-02-01 17:05:41.920 DEBUG taskExecutor-9 org.springframework.orm.jpa.JpaTransactionManager - - Initiating transaction commit_
_ 2019-02-01 17:10:06.193 DEBUG task-scheduler-6 org.springframework.orm.jpa.EntityManagerFactoryUtils - - Closing JPA EntityManager`_
#########################################################
**this error because procedure include miss typing spell “]” **
Look at the below.
I think that should type like
" DROP TABLE ul967t0pCodesets;]" <= NOT " DROP TABLE ul967t0pCodeset_s];_"
Somedody who know that how can edit that’s Quary Please~ TELL ME.