OHDSI Home | Forums | Wiki | Github

Error installing Achillies: relation "achilles_results" does not exist

Implementers
#1

Hello :smile:

I have a CDM compliant postgresql database, and i’m trying to launch Achilles atop it.
I had some bad starts with using docker to do this, and decided to switch to using R given that it was better at showing me errors, plus more widely used.
I worked my way down to step 5 of the getting started section of the [github]/OHDSI/Achilles page
When I try to execute exportToJson to generate Achilles statistics, I get an error, which runs,

exportToJson(connectionDetails, "odhsi", "odhsi", "/Users/snkasthu/Downloads/achillies_output");
Warning: folder /Users/snkasthu/Downloads/achillies_output already exists
Connecting using PostgreSQL driver
Generating condition treemap
  |                                                                      |   0%Error executing SQL: Error in rJava::.jcall(s, "Ljava/sql/ResultSet;", "executeQuery", as.character(query)[1]): org.postgresql.util.PSQLException: ERROR: relation "achilles_results" does not exist
  Position: 587

An error report has been created at  /Users/snkasthu/Achilles/errorReport.txt
Error in value[[3L]](cond) : no loop for break/next, jumping to top level

The detailed error report reads,

DBMS:
postgresql

Error:
org.postgresql.util.PSQLException: ERROR: relation “achilles_results” does not exist
Position: 587

SQL:
select concept_hierarchy.concept_id,
COALESCE(concept_hierarchy.soc_concept_name,‘NA’) || ‘||’ || COALESCE(concept_hierarchy.hlgt_concept_name,‘NA’) || ‘||’ || COALESCE(concept_hierarchy.hlt_concept_name,‘NA’) || ‘||’ || COALESCE(concept_hierarchy.pt_concept_name,‘NA’) || ‘||’ || COALESCE(concept_hierarchy.snomed_concept_name,‘NA’) concept_path,
ar1.count_value as num_persons,
ROUND(CAST(1.0ar1.count_value / denom.count_value AS NUMERIC),5) as percent_persons,
ROUND(CAST(1.0ar2.count_value / ar1.count_value AS NUMERIC),5) as records_per_person
from (select * from ACHILLES_results where analysis_id = 400) ar1
inner join
(select * from ACHILLES_results where analysis_id = 401) ar2
on ar1.stratum_1 = ar2.stratum_1
inner join
(
select snomed.concept_id,
snomed.concept_name as snomed_concept_name,
pt_to_hlt.pt_concept_name,
hlt_to_hlgt.hlt_concept_name,
hlgt_to_soc.hlgt_concept_name,
soc.concept_name as soc_concept_name
from
(
select concept_id, concept_name
from odhsi.concept
where vocabulary_id = 1
) snomed
left join
(select c1.concept_id as snomed_concept_id, max(c2.concept_id) as pt_concept_id
from
odhsi.concept c1
inner join
odhsi.concept_ancestor ca1
on c1.concept_id = ca1.descendant_concept_id
and c1.vocabulary_id = 1
inner join
odhsi.concept c2
on ca1.ancestor_concept_id = c2.concept_id
and c2.vocabulary_id = 15
and c2.concept_class = ‘Preferred Term’
group by c1.concept_id
) snomed_to_pt
on snomed.concept_id = snomed_to_pt.snomed_concept_id

  left join
  	(select c1.concept_id as pt_concept_id, c1.concept_name as pt_concept_name, max(c2.concept_id) as hlt_concept_id
  	from
  	odhsi.concept c1
  	inner join 
  	odhsi.concept_ancestor ca1
  	on c1.concept_id = ca1.descendant_concept_id
  	and c1.vocabulary_id = 15
  	and c1.concept_class = 'Preferred Term'
  	inner join 
  	odhsi.concept c2
  	on ca1.ancestor_concept_id = c2.concept_id
  	and c2.vocabulary_id = 15
  	and c2.concept_class = 'High Level Term'
  	group by c1.concept_id, c1.concept_name
  	) pt_to_hlt
  on snomed_to_pt.pt_concept_id = pt_to_hlt.pt_concept_id

  left join
  	(select c1.concept_id as hlt_concept_id, c1.concept_name as hlt_concept_name, max(c2.concept_id) as hlgt_concept_id
  	from
  	odhsi.concept c1
  	inner join 
  	odhsi.concept_ancestor ca1
  	on c1.concept_id = ca1.descendant_concept_id
  	and c1.vocabulary_id = 15
  	and c1.concept_class = 'High Level Term'
  	inner join 
  	odhsi.concept c2
  	on ca1.ancestor_concept_id = c2.concept_id
  	and c2.vocabulary_id = 15
  	and c2.concept_class = 'High Level Group Term'
  	group by c1.concept_id, c1.concept_name
  	) hlt_to_hlgt
  on pt_to_hlt.hlt_concept_id = hlt_to_hlgt.hlt_concept_id

  left join
  	(select c1.concept_id as hlgt_concept_id, c1.concept_name as hlgt_concept_name, max(c2.concept_id) as soc_concept_id
  	from
  	odhsi.concept c1
  	inner join 
  	odhsi.concept_ancestor ca1
  	on c1.concept_id = ca1.descendant_concept_id
  	and c1.vocabulary_id = 15
  	and c1.concept_class = 'High Level Group Term'
  	inner join 
  	odhsi.concept c2
  	on ca1.ancestor_concept_id = c2.concept_id
  	and c2.vocabulary_id = 15
  	and c2.concept_class = 'System Organ Class'
  	group by c1.concept_id, c1.concept_name
  	) hlgt_to_soc
  on hlt_to_hlgt.hlgt_concept_id = hlgt_to_soc.hlgt_concept_id

  left join odhsi.concept soc
   on hlgt_to_soc.soc_concept_id = soc.concept_id

) concept_hierarchy
on CAST(ar1.stratum_1 AS INT) = concept_hierarchy.concept_id
,
(select count_value from ACHILLES_results where analysis_id = 1) denom

order by ar1.count_value desc

R version:
R version 3.2.0 (2015-04-16)

Platform:
x86_64-apple-darwin13.4.0

Attached base packages:

  • stats
  • graphics
  • grDevices
  • utils
  • datasets
  • methods
  • base

Other attached packages:

  • Achilles (1.0)
  • rjson (0.2.15)
  • SqlRender (1.1.2)
  • DatabaseConnector (1.2.1)
  • RJDBC (0.2-5)
  • rJava (0.9-6)
  • DBI (0.3.1)
  • devtools (1.8.0)

Any suggestions on why i’m missing this relation would be very much appreciated :smile:

#2

I have the same issue with missing « achilles.cohort » relation (Position : 787) when I specify analysisIds = c(1).
I don’t understand when should be created achilles.cohort, achilles_results tables.

t