OHDSI Home | Forums | Wiki | Github

How can i edit code(sql)


(Park,CHANGHYUN) #1

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.


(Chris Knoll) #2

The message 일괄처리 작업 중 오류가 발생했습니다: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다_ translates to “table or view does not exist”. But i don’t think it’s the temp tables (although it could be) but possibly instead that your results schema is not set up.

Some discussion on resolving this is here: Cohort Generation in Atlas with Generation status Failed

Could you try to go into atlas and export your cohort SQL and try to execute it by hand? If you do it in chunks, we can better understand which tables might not exist.

If you are able to completely execute the SQL from Atlas UI, then the problem is that there are additional tables that need to be created in your results schema (which is listed in the above forum thread).

Could you try to execute your SQL manually and double check the tables in the above thread exist in your results schema?

Edit: I notice that the log seems to have the complete sql in it that you can copy and run the individual statements in a Oracle DB console. So instead of copying out of Atlas, you can jsut take the entire sql and run it one-line-at-a-time so you can narrow down exactly which statement claims the table/view does not exist.


t