Hi OHDSI Community,
We are right now running into some strange report errors with a potential ATLAS deployment. We checked and we are running the latest version of ATLAS (commit: b13d793; version: 2.12.0). Any ideas why we could currently see failures for the following reports on our OMOP CDM instance? I’ve attached error messages to tables that failed where we could capture errors:
- Person
- Dashboard
- Measurement
Error Message for Measurement
SELECT `concept_hierarchy`.`concept_id` `concept_id`,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT((CASE WHEN
(NOT (`concept_hierarchy`.`level3_concept_name` IS NULL)) THEN
`concept_hierarchy`.`level3_concept_name` WHEN (NOT ('NA' IS NULL))
THEN 'NA' ELSE NULL END), '||'), (CASE WHEN (NOT
(`concept_hierarchy`.`level2_concept_name` IS NULL)) THEN
`concept_hierarchy`.`level2_concept_name` WHEN (NOT ('NA' IS NULL))
THEN 'NA' ELSE NULL END)), '||'), (CASE WHEN (NOT
(`concept_hierarchy`.`level1_concept_name` IS NULL)) THEN
`concept_hierarchy`.`level1_concept_name` WHEN (NOT ('NA' IS NULL))
THEN 'NA' ELSE NULL END)), '||'), (CASE WHEN (NOT
(`concept_hierarchy`.`concept_name` IS NULL)) THEN
`concept_hierarchy`.`concept_name` WHEN (NOT ('NA' IS NULL)) THEN
'NA' ELSE NULL END)) `concept_path`,
`ar1`.`omop_v5_achilles_resultscount_value` `num_persons`, ((1.0 *
`ar1`.`omop_v5_achilles_resultscount_value`) /
`denom`.`omop_v5_achilles_resultscount_value`) `percent_persons`, ((1.0 *
`ar2`.`omop_v5_achilles_resultscount_value`) /
`ar1`.`omop_v5_achilles_resultscount_value`) `records_per_person` FROM
(SELECT `omop_v5_achilles_results`.`analysis_id`
`omop_v5_achilles_resultsanalysis_id`,
`omop_v5_achilles_results`.`stratum_1`
`omop_v5_achilles_resultsstratum_1`,
`omop_v5_achilles_results`.`stratum_2`
`omop_v5_achilles_resultsstratum_2`,
`omop_v5_achilles_results`.`stratum_3`
`omop_v5_achilles_resultsstratum_3`,
`omop_v5_achilles_results`.`stratum_4`
`omop_v5_achilles_resultsstratum_4`,
`omop_v5_achilles_results`.`stratum_5`
`omop_v5_achilles_resultsstratum_5`,
`omop_v5_achilles_results`.`count_value`
`omop_v5_achilles_resultscount_value` FROM
`test_schema`.`omop_v5`.`achilles_results` `omop_v5_achilles_results`
WHERE `omop_v5_achilles_results`.`analysis_id`=1800) `ar1` JOIN
(SELECT `omop_v5_achilles_results`.`analysis_id`
`omop_v5_achilles_resultsanalysis_id`,
`omop_v5_achilles_results`.`stratum_1`
`omop_v5_achilles_resultsstratum_1`,
`omop_v5_achilles_results`.`stratum_2`
`omop_v5_achilles_resultsstratum_2`,
`omop_v5_achilles_results`.`stratum_3`
`omop_v5_achilles_resultsstratum_3`,
`omop_v5_achilles_results`.`stratum_4`
`omop_v5_achilles_resultsstratum_4`,
`omop_v5_achilles_results`.`stratum_5`
`omop_v5_achilles_resultsstratum_5`,
`omop_v5_achilles_results`.`count_value`
`omop_v5_achilles_resultscount_value` FROM
`test_schema`.`omop_v5`.`achilles_results` `omop_v5_achilles_results`
WHERE `omop_v5_achilles_results`.`analysis_id`=1801) `ar2` ON (
`ar1`.`omop_v5_achilles_resultsstratum_1`=`ar2`.`omop_v5_achilles_results
stratum_1` ) JOIN `test_schema`.`omop_v5`.`concept_hierarchy`
`concept_hierarchy` ON ( ( CAST( (CASE WHEN (CASE WHEN (NOT
(CAST( `ar1`.`stratum_1` AS double ) IS NULL)) THEN 1 ELSE 0 END)=1
THEN `ar1`.`stratum_1` ELSE NULL END) AS int
)=`concept_hierarchy`.`concept_id` AND
`concept_hierarchy`.`treemap`='Measurement' ) ) CROSS JOIN (SELECT
`omop_v5_achilles_results`.`count_value`
`omop_v5_achilles_resultscount_value` FROM
`test_schema`.`omop_v5`.`achilles_results` `omop_v5_achilles_results`
WHERE `omop_v5_achilles_results`.`analysis_id`=1) `denom` ORDER BY
`ar1`.`omop_v5_achilles_resultscount_value` DESC
- Observation
- Observation Period
Error Message for Observation Period
SELECT 'Length of observation' `seriesName`, `ar1`.`xLengthOfObservation`
`xLengthOfObservation`, ROUND(CAST( ((1.0 *
SUM(`ar2`.`omop_v5_achilles_resultscount_value`)) /
`denom`.`omop_v5_achilles_resultscount_value`) AS double ), 5)
`yPercentPersons` FROM (SELECT `ar`.`analysis_id` `aranalysis_id`,
`ar`.`stratum_1` `arstratum_1`, `ar`.`stratum_2` `arstratum_2`,
`ar`.`stratum_3` `arstratum_3`, `ar`.`stratum_4` `arstratum_4`,
`ar`.`stratum_5` `arstratum_5`, `ar`.`count_value` `arcount_value`, (CAST(
(CASE WHEN (CASE WHEN (NOT (CAST( `ar`.`stratum_1` AS double ) IS
NULL)) THEN 1 ELSE 0 END)=1 THEN `ar`.`stratum_1` ELSE CAST(NULL
AS STRING) END) AS int ) * 30) `xLengthOfObservation` FROM
`test_schema`.`omop_v5`.`achilles_results` `ar` WHERE
`ar`.`analysis_id`=108) `ar1` CROSS JOIN (SELECT
`omop_v5_achilles_results`.`analysis_id`
`omop_v5_achilles_resultsanalysis_id`,
`omop_v5_achilles_results`.`stratum_1`
`omop_v5_achilles_resultsstratum_1`,
`omop_v5_achilles_results`.`stratum_2`
`omop_v5_achilles_resultsstratum_2`,
`omop_v5_achilles_results`.`stratum_3`
`omop_v5_achilles_resultsstratum_3`,
`omop_v5_achilles_results`.`stratum_4`
`omop_v5_achilles_resultsstratum_4`,
`omop_v5_achilles_results`.`stratum_5`
`omop_v5_achilles_resultsstratum_5`,
`omop_v5_achilles_results`.`count_value`
`omop_v5_achilles_resultscount_value` FROM
`test_schema`.`omop_v5`.`achilles_results` `omop_v5_achilles_results`
WHERE `omop_v5_achilles_results`.`analysis_id`=108) `ar2` WHERE ( (
`ar1`.`aranalysis_id`=`ar2`.`omop_v5_achilles_resultsanalysis_id` AND
CAST( (CASE WHEN (CASE WHEN (NOT (CAST( `ar1`.`stratum_1` AS
double ) IS NULL)) THEN 1 ELSE 0 END)=1 THEN `ar1`.`stratum_1` ELSE
NULL END) AS int )<=CAST( (CASE WHEN (CASE WHEN (NOT (CAST(
`ar2`.`stratum_1` AS double ) IS NULL)) THEN 1 ELSE 0 END)=1 THEN
`ar2`.`stratum_1` ELSE NULL END) AS int ) ) ) CROSS JOIN (SELECT
`omop_v5_achilles_results`.`count_value`
`omop_v5_achilles_resultscount_value` FROM
`test_schema`.`omop_v5`.`achilles_results` `omop_v5_achilles_results`
AND ( `omop_v5_achilles_results`.`analysis_id`=1 )) `denom` GROUP BY
`ar1`.`xLengthOfObservation`,
`denom`.`omop_v5_achilles_resultscount_value` ORDER BY
xLengthOfObservation ASC
error is: mismatched input 'CROSS' expecting {<EOF>, ';'}(line 1, pos 1855)
I am happy to add additional information so we could determine what may be producing these errors. Thank you!
~ tcp 