Sorry, shoulda had that handy, I know better. The below highlights the varying CTE errors against service versions.
Using intersystems-jdbc-3.10.3.jar from maven and
Service:
IRIS LOCKEDDOWN ARM64 / 2024.2.0.247.0
8.0.1 1.17 01/10/25 iris-lockeddown-arm64:2024.2.0.247.0
Throws:
DBMS:
iris
Error:
java.sql.SQLException: [SQLCODE: <-383>:<CTE is only supported for SELECT statements>]
[Location: <Prepare>]
[%msg: < CTE is only supported for SELECT statements.>]
SQL:
WITH rawData AS (
select
year(op1.index_date) - p1.YEAR_OF_BIRTH as stratum_1,
COUNT(p1.person_id) as count_value
from OMOPCDM54.person p1
inner join (select person_id, MIN(observation_period_start_date) as index_date from OMOPCDM54.observation_period group by PERSON_ID) op1
on p1.PERSON_ID = op1.PERSON_ID
group by year(op1.index_date) - p1.YEAR_OF_BIRTH
)
CREATE GLOBAL TEMPORARY TABLE OMOPCDM54_RESULTS.ku80ymnms_tmpach_101
AS SELECT 101 as analysis_id,
CAST(stratum_1 AS VARCHAR(255)) as stratum_1,
cast(null as varchar(255)) as stratum_2,
cast(null as varchar(255)) as stratum_3,
cast(null as varchar(255)) as stratum_4,
cast(null as varchar(255)) as stratum_5,
count_value
FROM rawData
R version:
R version 4.2.1 (2022-06-23)
Platform:
x86_64-pc-linux-gnu
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- SqlRender (1.19.1)
- Achilles (1.7.2)
- DatabaseConnector (6.4.0)
- Andromeda (0.6.7)
- dplyr (1.1.4)
Service:
IRIS LOCKEDDOWN ARM64 / 2024.3.0.217.0
8.0.4 1.17 02/03/25 iris-lockeddown-arm64:2024.3.0.217.0 N/A
Throws:
DBMS:
iris
Error:
java.sql.SQLException: [SQLCODE: <-383>:<CTE is only supported for SELECT, INSERT, UPDATE, DELETE statements, or in the query following CREATE TABLE ... AS>]
[Location: <Prepare>]
[%msg: < CTE is only supported for SELECT, INSERT, UPDATE, DELETE statements, or in the query following CREATE TABLE ... AS.>]
SQL:
with rawData (gender_concept_id, age_value) as
(
select p.gender_concept_id, MIN(YEAR(observation_period_start_date)) - P.YEAR_OF_BIRTH as age_value
from OMOPCDM54.person p
JOIN OMOPCDM54.observation_period op on p.person_id = op.person_id
group by p.person_id,p.gender_concept_id, p.year_of_birth
),
overallStats (gender_concept_id, avg_value, stdev_value, min_value, max_value, total) as
(
select gender_concept_id,
CAST(avg(1.0 * age_value) AS FLOAT) as avg_value,
CAST(STDDEV(age_value) AS FLOAT) as stdev_value,
min(age_value) as min_value,
max(age_value) as max_value,
COUNT(*) as total
FROM rawData
group by gender_concept_id
),
ageStats (gender_concept_id, age_value, total, rn) as
(
select gender_concept_id, age_value, COUNT(*) as total, row_number() over (order by age_value) as rn
FROM rawData
group by gender_concept_id, age_value
),
ageStatsPrior (gender_concept_id, age_value, total, accumulated) as
(
select s.gender_concept_id, s.age_value, s.total, sum(p.total) as accumulated
from ageStats s
join ageStats p on s.gender_concept_id = p.gender_concept_id and p.rn <= s.rn
group by s.gender_concept_id, s.age_value, s.total, s.rn
)
CREATE GLOBAL TEMPORARY TABLE OMOPCDM54_RESULTS.iq6fwdx8tempResults_104
AS SELECT 104 as analysis_id,
CAST(o.gender_concept_id AS VARCHAR(255)) as stratum_1,
o.total as count_value,
o.min_value,
o.max_value,
o.avg_value,
o.stdev_value,
MIN(case when p.accumulated >= .50 * o.total then age_value end) as median_value,
MIN(case when p.accumulated >= .10 * o.total then age_value end) as p10_value,
MIN(case when p.accumulated >= .25 * o.total then age_value end) as p25_value,
MIN(case when p.accumulated >= .75 * o.total then age_value end) as p75_value,
MIN(case when p.accumulated >= .90 * o.total then age_value end) as p90_value
FROM ageStatsPrior p
join overallStats o on p.gender_concept_id = o.gender_concept_id
GROUP BY o.gender_concept_id, o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
R version:
R version 4.2.1 (2022-06-23)
Platform:
x86_64-pc-linux-gnu
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- SqlRender (1.19.1)
- Achilles (1.7.2)
- DatabaseConnector (6.4.0)
- Andromeda (0.6.7)
- dplyr (1.1.4)
Local (EAP Intsall)
USER>zw $ZV "IRIS for UNIX (Ubuntu Server 24.04 LTS for x86-64) 2025.1.0VECANN (Build 124U) Tue Jan 21 2025 17:22:13 EST"
Throws:
DBMS:
iris
Error:
java.sql.SQLException: [SQLCODE: <-383>:<CTE is only supported for SELECT, INSERT, UPDATE, DELETE statements, or in the query following CREATE TABLE ... AS>]
[Location: <Prepare>]
[%msg: < CTE is only supported for SELECT, INSERT, UPDATE, DELETE statements, or in the query following CREATE TABLE ... AS.>]
SQL:
WITH rawData AS (
SELECT
m.measurement_concept_id AS stratum_1,
YEAR(m.measurement_date) AS stratum_2,
p.gender_concept_id AS stratum_3,
FLOOR((YEAR(m.measurement_date) - p.year_of_birth) / 10) AS stratum_4,
COUNT(DISTINCT p.person_id) AS count_value
FROM
OMOPCDM54.person p
JOIN
OMOPCDM54.measurement m
ON
p.person_id = m.person_id
JOIN
OMOPCDM54.observation_period op
ON
m.person_id = op.person_id
AND
m.measurement_date >= op.observation_period_start_date
AND
m.measurement_date <= op.observation_period_end_date
GROUP BY
m.measurement_concept_id,
YEAR(m.measurement_date),
p.gender_concept_id,
FLOOR((YEAR(m.measurement_date) - p.year_of_birth) / 10)
)
CREATE GLOBAL TEMPORARY TABLE OMOPCDM54_RESULTS.bqtg620qs_tmpach_1804
AS SELECT 1804 AS analysis_id,
CAST(stratum_1 AS VARCHAR(255)) AS stratum_1,
CAST(stratum_2 AS VARCHAR(255)) AS stratum_2,
CAST(stratum_3 AS VARCHAR(255)) AS stratum_3,
CAST(stratum_4 AS VARCHAR(255)) AS stratum_4,
CAST(NULL AS VARCHAR(255)) AS stratum_5,
count_value
FROM rawData
R version:
R version 4.2.1 (2022-06-23)
Platform:
x86_64-pc-linux-gnu
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- SqlRender (1.19.1)
- Achilles (1.7.2)
- DatabaseConnector (6.4.0)
- Andromeda (0.6.7)
- dplyr (1.1.4)
On ALL achilles jobs, after the CTE errors, they all end with this error:
Thread: Main
Message: line 37 did not have 6 elements
Level: FATAL
Time: 2025-02-14 17:48:16
Stack trace:
7: scan(file = file, what = what, sep = sep, quote = quote, dec = dec, nmax = nrows, skip = 0, na.strings = na.strings, quiet = TRUE, fill = fill, strip.white = strip.white, blank.lines.skip = blank.lines.skip, multi.line = FALSE, comment.char = comment.ch
6: utils::read.table(file = file.path(outputFolder, "log_achilles.txt"), header = FALSE, sep = "\t", stringsAsFactors = FALSE)
5: .parseLogs(outputFolder)
4: .mergeAchillesScratchTables(resultsTable = table, connectionDetails = connectionDetails, analysisIds = analysisDetails$ANALYSIS_ID, createTable = createTable, schemaDelim = schemaDelim, scratchDatabaseSchema = scratchDatabaseSchema, resultsDatabaseSchem
3: FUN(X[[i]], ...)
2: lapply(resultsTablesToMerge, function(table) {
.mergeAchillesScratchTables(resultsTable = table, connectionDetails = connectionDetails, analysisIds = analysisDetails$ANALYSIS_ID, createTable = createTable, schemaDelim = schemaDelim, scratchDatabaseSc
1: achilles(connectionDetails = connectionDetails, cdmDatabaseSchema = "OMOPCDM54", cdmVersion = "5.4", resultsDatabaseSchema = "OMOPCDM54_RESULTS", outputFolder = "output", optimizeAtlasCache = TRUE, createTable = TRUE)
R version:
R version 4.2.1 (2022-06-23)
Platform:
x86_64-pc-linux-gnu
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- SqlRender (1.19.1)
- Achilles (1.7.2)
- DatabaseConnector (6.4.0)
- Andromeda (0.6.7)
- dplyr (1.1.4)