OHDSI Home | Forums | Wiki | Github

Characterization executions error

atlas
webapi

(Summer) #1
org.ohdsi.webapi.exception.AtlasException: java.lang.RuntimeException: java.util.concurrent.ExecutionException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP TABLE IF EXISTS cov_ref; DROP TABLE IF EXISTS analysis_ref; CREATE TEMP TABLE cov_ref  (covariate_id BIGINT,
	covariate_name VARCHAR(512),
	analysis_id INT,
	concept_id INT
	); CREATE TEMP TABLE analysis_ref  (analysis_id BIGINT,
	analysis_name VARCHAR(512),
	domain_id VARCHAR(20),
	
	start_day INT,
	end_day INT,

	is_binary VARCHAR(1),
	missing_means_zero VARCHAR(1)
	); DROP TABLE IF EXISTS concept_count_data; DROP TABLE IF EXISTS concept_count_stats; DROP TABLE IF EXISTS concept_count_prep; DROP TABLE IF EXISTS concept_count_prep2; CREATE TEMP TABLE concept_count_data


AS
SELECT
cohort_definition_id,
	subject_id,
	cohort_start_date,
	

	concept_count

FROM
(
	SELECT 
	


		cohort_definition_id,
		subject_id,
		cohort_start_date,


		COUNT(*) AS concept_count

	FROM results.temp_cohort_oflsq4ja cohort
	INNER JOIN faers.visit_occurrence
		ON cohort.subject_id = visit_occurrence.person_id

	WHERE visit_start_date <= (cohort.cohort_start_date + 0*INTERVAL'1 day')
		AND visit_end_date >= (cohort.cohort_start_date + -180*INTERVAL'1 day')
		AND visit_concept_id != 0



		AND cohort.cohort_definition_id IN (2)
	GROUP BY 
	
 

		cohort_definition_id,
		subject_id,
		cohort_start_date
	
	) raw_data; ANALYZE concept_count_data

; CREATE TEMP TABLE concept_count_stats

AS
WITH t1  AS (
	SELECT cohort_definition_id,
		COUNT(*) AS cnt 
	FROM results.temp_cohort_oflsq4ja 
	WHERE cohort_definition_id IN (2)
	GROUP BY cohort_definition_id
	),
t2 AS (
	SELECT cohort_definition_id,
		COUNT(*) AS cnt, 
 
		MIN(concept_count) AS min_concept_count, 
		MAX(concept_count) AS max_concept_count, 
		SUM(CAST(concept_count AS BIGINT)) AS sum_concept_count,
		SUM(CAST(concept_count AS BIGINT) * CAST(concept_count AS BIGINT)) AS squared_concept_count
	FROM concept_count_data
	GROUP BY cohort_definition_id
 
	)
 SELECT
t1.cohort_definition_id,
	CASE WHEN t2.cnt = t1.cnt THEN t2.min_concept_count ELSE 0 END AS min_value,
	t2.max_concept_count AS max_value,
 
	CAST(t2.sum_concept_count / (1.0 * t1.cnt) AS NUMERIC) AS average_value,
	CAST(CASE
		WHEN t2.cnt = 1 THEN 0 
		ELSE SQRT((1.0 * t2.cnt*t2.squared_concept_count - 1.0 * t2.sum_concept_count*t2.sum_concept_count) / (1.0 * t2.cnt*(1.0 * t2.cnt - 1))) 
	END AS NUMERIC) AS standard_deviation,
	t2.cnt AS count_value,
	t1.cnt - t2.cnt AS


Caused by: org.postgresql.util.PSQLException: ERROR: column “temp.cohort_definition_id” must appear in the GROUP BY clause or be used in an aggregate function.


(Summer) #2

This error only appeared when you choose the Hospital Frailty Risk Score Feature analyses in Characterization.


(Chris Knoll) #3

@schuemie: do you know who’s maintainer of this repo now, or do you know the implementation of ‘Hospital Fraility Risk Score’? It may not be handling aggregating to cohort_definition_id properly.


(Martijn Schuemie) #4

@anthonysena is the maintainer of the FeatureExtraction repo.


(Anthony Sena) #5

Hi @UltraXiaoZi - I’ve posted this issue to GitHub and will take a look.


t