In our environment, executing multiple visualization packs in Heracles (e.g. person and observation) results in batch job failure and WebAPI reports the exception and stack trace provided below. We are using MSSQL.
2015-10-08 14:32:11.756 ERROR taskExecutor-1 org.springframework.batch.core.step.AbstractStep - - Encountered an error executing step cohortAnalysisStep in job cohortAnalysisJob
org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL[
DELETE FROM my_omop.dbo.HERACLES_results
WHERE cohort_definition_id IN (12) AND analysis_id IN (116, 117, 0, 1, 2, 3, 4, 5, 200, 201, 202, 203, 204, 206, 211);
DELETE FROM my_omop.dbo.HERACLES_results_dist
WHERE cohort_definition_id IN (12) AND analysis_id IN (116, 117, 0, 1, 2, 3, 4, 5, 200, 201, 202,
203, 204, 206, 211);
IF OBJECT_ID('tempdb..#HERACLES_cohort', 'U') IS NOT NULL
DROP TABLE #HERACLES_cohort;
SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
INTO #HERACLES_cohort
FROM my_omop.dbo.cohort
WHERE cohort_definition_id IN (12);
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
0 AS analysis_id,
'cumc' AS stratum_1,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id;
INSERT INTO my_omop.dbo.HERACLES_results_dist (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
0 AS analysis_id,
'cumc' AS stratum_1,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, count_value)
SELECT
c1.cohort_definition_id,
1 AS analysis_id,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
2 AS analysis_id,
gender_concept_id AS stratum_1,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id, GENDER_CONCEPT_ID;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
3 AS analysis_id,
year_of_birth AS stratum_1,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id, YEAR_OF_BIRTH;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
4 AS analysis_id,
RACE_CONCEPT_ID AS stratum_1,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id, RACE_CONCEPT_ID;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
5 AS analysis_id,
ETHNICITY_CONCEPT_ID AS stratum_1,
COUNT_BIG(DISTINCT person_id) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
GROUP BY c1.cohort_definition_id, ETHNICITY_CONCEPT_ID;
IF OBJECT_ID('temp_dates', 'U') IS NOT NULL --This should only do something in Oracle
DROP TABLE temp_dates;
SELECT DISTINCT YEAR(observation_period_start_date) AS obs_year
INTO
#temp_dates
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
INNER JOIN
my_omop.dbo.observation_period op1
ON p1.person_id = op1.person_id;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, stratum_2, stratum_3, count_value)
SELECT
c1.cohort_definition_id,
116 AS analysis_id,
t1.obs_year AS stratum_1,
p1.gender_concept_id AS stratum_2,
floor((t1.obs_year - p1.year_of_birth) / 10) AS stratum_3,
COUNT_BIG(DISTINCT p1.PERSON_ID) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
INNER JOIN
my_omop.dbo.observation_period op1
ON p1.person_id = op1.person_id
,
#temp_dates t1
WHERE year(op1.OBSERVATION_PERIOD_START_DATE) <= t1.obs_year
AND year(op1.OBSERVATION_PERIOD_END_DATE) >= t1.obs_year
GROUP BY c1.cohort_definition_id,
t1.obs_year,
p1.gender_concept_id,
floor((t1.obs_year - p1.year_of_birth) / 10);
TRUNCATE TABLE #temp_dates;
DROP TABLE #temp_dates;
IF OBJECT_ID('temp_dates', 'U') IS NOT NULL --This should only do something in Oracle
DROP TABLE temp_dates;
SELECT DISTINCT YEAR(observation_period_start_date) * 100 + MONTH(observation_period_start_date) AS obs_month
INTO
#temp_dates
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
INNER JOIN
my_omop.dbo.observation_period op1
ON p1.person_id = op1.person_id;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
117 AS analysis_id,
t1.obs_month AS stratum_1,
COUNT_BIG(DISTINCT op1.PERSON_ID) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
INNER JOIN
my_omop.dbo.observation_period op1
ON p1.person_id = op1.person_id
,
#temp_dates t1
WHERE YEAR(observation_period_start_date) * 100 + MONTH(observation_period_start_date) <= t1.obs_month
AND YEAR(observation_period_end_date) * 100 + MONTH(observation_period_end_date) >= t1.obs_month
GROUP BY c1.cohort_definition_id, t1.obs_month;
TRUNCATE TABLE #temp_dates;
DROP TABLE #temp_dates;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
200 AS analysis_id,
--
--
vo1.visit_CONCEPT_ID AS stratum_1,
--
COUNT_BIG(DISTINCT vo1.PERSON_ID) AS count_value
FROM
my_omop.dbo.visit_occurrence vo1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON vo1.person_id = c1.subject_id
--
GROUP BY c1.cohort_definition_id,
--
--
vo1.visit_CONCEPT_ID
--
;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, count_value)
SELECT
c1.cohort_definition_id,
201 AS analysis_id,
--
--
vo1.visit_CONCEPT_ID AS stratum_1,
--
COUNT_BIG(vo1.PERSON_ID) AS count_value
FROM
my_omop.dbo.visit_occurrence vo1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON vo1.person_id = c1.subject_id
--
GROUP BY c1.cohort_definition_id,
--
--
vo1.visit_CONCEPT_ID
--
;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, stratum_2, count_value)
SELECT
c1.cohort_definition_id,
202 AS analysis_id,
--
--
vo1.visit_CONCEPT_ID AS stratum_1,
--
YEAR(visit_start_date) * 100 + month(visit_start_date) AS stratum_2,
COUNT_BIG(DISTINCT PERSON_ID) AS count_value
FROM
my_omop.dbo.visit_occurrence vo1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON vo1.person_id = c1.subject_id
--
GROUP BY c1.cohort_definition_id,
--
--
vo1.visit_CONCEPT_ID,
--
YEAR(visit_start_date) * 100 + month(visit_start_date);
INSERT INTO my_omop.dbo.HERACLES_results_dist (cohort_definition_id, analysis_id, count_value, min_value, max_value, avg_value, stdev_value, median_value, p10_value, p25_value, p75_value, p90_value)
SELECT
cohort_definition_id,
203 AS analysis_id,
COUNT_BIG(count_value) AS count_value,
min(count_value) AS min_value,
max(count_value) AS max_value,
avg(1.0 * count_value) AS avg_value,
stdev(count_value) AS stdev_value,
max(CASE WHEN p1 <= 0.50
THEN count_value
ELSE -9999 END) AS median_value,
max(CASE WHEN p1 <= 0.10
THEN count_value
ELSE -9999 END) AS p10_value,
max(CASE WHEN p1 <= 0.25
THEN count_value
ELSE -9999 END) AS p25_value,
max(CASE WHEN p1 <= 0.75
THEN count_value
ELSE -9999 END) AS p75_value,
max(CASE WHEN p1 <= 0.90
THEN count_value
ELSE -9999 END) AS p90_value
FROM
(
SELECT
cohort_definition_id,
num_visits AS count_value,
1.0 * (row_number()
OVER (PARTITION BY cohort_definition_id
ORDER BY num_visits)) / (COUNT_BIG(num_visits)
OVER (PARTITION BY cohort_definition_id) + 1) AS p1
FROM
(
SELECT
c1.cohort_definition_id,
vo1.person_id,
--
--
COUNT_BIG(DISTINCT vo1.visit_concept_id) AS num_visits
--
FROM
my_omop.dbo.visit_occurrence vo1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON vo1.person_id = c1.subject_id
--
GROUP BY c1.cohort_definition_id, vo1.person_id
) t0
) t1
GROUP BY cohort_definition_id;
INSERT INTO my_omop.dbo.HERACLES_results (cohort_definition_id, analysis_id, stratum_1, stratum_2, stratum_3, stratum_4, count_value)
SELECT
c1.cohort_definition_id,
204 AS analysis_id,
--
--
vo1.visit_CONCEPT_ID AS stratum_1,
--
YEAR(visit_start_date) AS stratum_2,
p1.gender_concept_id AS stratum_3,
floor((year(visit_start_date) - p1.year_of_birth) / 10) AS stratum_4,
COUNT_BIG(DISTINCT p1.PERSON_ID) AS count_value
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
INNER JOIN
my_omop.dbo.visit_occurrence vo1
ON p1.person_id = vo1.person_id
--
GROUP BY c1.cohort_definition_id,
--
--
vo1.visit_CONCEPT_ID,
--
YEAR(visit_start_date),
p1.gender_concept_id,
floor((year(visit_start_date) - p1.year_of_birth) / 10);
INSERT INTO my_omop.dbo.HERACLES_results_dist (cohort_definition_id, analysis_id, stratum_1, stratum_2, count_value, min_value, max_value, avg_value, stdev_value, median_value, p10_value, p25_value, p75_value, p90_value)
SELECT
cohort_definition_id,
206 AS analysis_id,
visit_CONCEPT_ID AS stratum_1,
gender_concept_id AS stratum_2,
COUNT_BIG(count_value) AS count_value,
min(count_value) AS min_value,
max(count_value) AS max_value,
avg(1.0 * count_value) AS avg_value,
stdev(count_value) AS stdev_value,
max(CASE WHEN p1 <= 0.50
THEN count_value
ELSE -9999 END) AS median_value,
max(CASE WHEN p1 <= 0.10
THEN count_value
ELSE -9999 END) AS p10_value,
max(CASE WHEN p1 <= 0.25
THEN count_value
ELSE -9999 END) AS p25_value,
max(CASE WHEN p1 <= 0.75
THEN count_value
ELSE -9999 END) AS p75_value,
max(CASE WHEN p1 <= 0.90
THEN count_value
ELSE -9999 END) AS p90_value
FROM
(
SELECT
c1.cohort_definition_id,
--
--
vo1.visit_CONCEPT_ID,
--
p1.gender_concept_id,
vo1.visit_start_year - p1.year_of_birth AS count_value,
1.0 * (row_number()
OVER (PARTITION BY c1.cohort_definition_id, vo1.visit_CONCEPT_ID, p1.gender_concept_id
ORDER BY vo1.visit_start_year - p1.year_of_birth)) / (COUNT_BIG(vo1.visit_start_year - p1.year_of_birth)
OVER (PARTITION BY c1.cohort_definition_id, vo1.visit_CONCEPT_ID, p1.gender_concept_id)
+ 1) AS p1
FROM my_omop.dbo.PERSON p1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON p1.person_id = c1.subject_id
INNER JOIN
(SELECT
vo0.person_id,
--
--
vo0.visit_CONCEPT_ID,
--
min(year(vo0.visit_start_date)) AS visit_start_year
FROM my_omop.dbo.visit_occurrence vo0
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON vo0.person_id = c1.subject_id
--
GROUP BY person_id,
--
--
vo0.visit_CONCEPT_ID
--place_of_service_concept_id
) vo1
ON p1.person_id = vo1.person_id
) t1
GROUP BY cohort_definition_id,
visit_CONCEPT_ID,
gender_concept_id;
INSERT INTO my_omop.dbo.HERACLES_results_dist (cohort_definition_id, analysis_id, stratum_1, count_value, min_value, max_value, avg_value, stdev_value, median_value, p10_value, p25_value, p75_value, p90_value)
SELECT
cohort_definition_id,
211 AS analysis_id,
--
--
visit_CONCEPT_ID
--
AS stratum_1,
COUNT_BIG(count_value) AS count_value,
min(count_value) AS min_value,
max(count_value) AS max_value,
avg(1.0 * count_value) AS avg_value,
stdev(count_value) AS stdev_value,
max(CASE WHEN p1 <= 0.50
THEN count_value
ELSE -9999 END) AS median_value,
max(CASE WHEN p1 <= 0.10
THEN count_value
ELSE -9999 END) AS p10_value,
max(CASE WHEN p1 <= 0.25
THEN count_value
ELSE -9999 END) AS p25_value,
max(CASE WHEN p1 <= 0.75
THEN count_value
ELSE -9999 END) AS p75_value,
max(CASE WHEN p1 <= 0.90
THEN count_value
ELSE -9999 END) AS p90_value
FROM
(
SELECT
c1.cohort_definition_id,
--
--
vo1.visit_CONCEPT_ID,
--
datediff(DD, visit_start_date, visit_end_date) AS count_value,
1.0 * (row_number()
OVER (PARTITION BY c1.cohort_definition_id, vo1.visit_CONCEPT_ID
ORDER BY datediff(DD, visit_start_date, visit_end_date))) /
(COUNT_BIG(datediff(DD, visit_start_date, visit_end_date))
OVER (PARTITION BY c1.cohort_definition_id, vo1.visit_CONCEPT_ID) + 1) AS p1
FROM my_omop.dbo.visit_occurrence vo1
INNER JOIN (SELECT
subject_id,
cohort_definition_id,
cohort_start_date,
cohort_end_date
FROM #HERACLES_cohort) c1
ON vo1.person_id = c1.subject_id
--
) t1
GROUP BY cohort_definition_id,
--
--
visit_CONCEPT_ID
--
;
TRUNCATE TABLE #HERACLES_cohort;
DROP TABLE #HERACLES_cohort;
DELETE FROM my_omop.dbo.HERACLES_results
WHERE count_value <= 10;
DELETE FROM my_omop.dbo.HERACLES_results_dist
WHERE count_value <= 10
]; SQL state [S0001]; error code [2714]; There is already an object named '#temp_dates' in the database.; nested exception is java.sql.BatchUpdateException: There is already an object named '#temp_dates' in the database.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:611)
at org.ohdsi.webapi.cohortanalysis.CohortAnalysisTasklet$1.doInTransaction(CohortAnalysisTasklet.java:61)
at org.ohdsi.webapi.cohortanalysis.CohortAnalysisTasklet$1.doInTransaction(CohortAnalysisTasklet.java:44)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
at org.ohdsi.webapi.cohortanalysis.CohortAnalysisTasklet.execute(CohortAnalysisTasklet.java:44)
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:271)
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
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:198)
at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
at org.springframework.batch.core.job.AbstractJob.handleStep(AbstractJob.java:386)
at org.springframework.batch.core.job.SimpleJob.doExecute(SimpleJob.java:135)
at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:304)
at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.BatchUpdateException: There is already an object named '#temp_dates' in the database.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:572)
at org.springframework.jdbc.core.JdbcTemplate$1BatchUpdateStatementCallback.doInStatement(JdbcTemplate.java:559)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
... 23 more
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent