OHDSI Home | Forums | Wiki | Github

Invalid object name 'shs_oklahoma.shs_oklahoma_achilles.achilles_results'


(Kai Post) #1

After finally being able to run all analyses using Docker and the Achilles CDM6 2020 version, I am getting an error message that the table “achilles_results” does not exist. Any ideas what might be going on? Don’t see anything obvious in the error file yet either.

Analysis 2201 (Number of note records, by note_type_concept_id) – START
|======================================================================| 100%
Executing SQL took 0.135 secs
[Main Analysis] [COMPLETE] 2201 (0.144284 secs)
Error in .mergeAchillesScratchTables(resultsTable = table, connectionDetails = connectionDetails, :
unused argument (cdmVersionFolder = cdmVersionFolder)
Calls: achilles -> lapply -> FUN
Warning: folder DEFAULT/DEFAULT/2020-02-27T00.44.05 already exists
Connecting using SQL Server driver
Generating condition treemap
| | 0%Error: Error executing SQL:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs_oklahoma.shs_oklahoma_achilles.achilles_results’.
An error report has been created at /opt/app/errorReport.txt


(Kai Post) #2

Contents of errorReport.txt:

DBMS:
sql server

Error:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs_oklahoma.shs_oklahoma_achilles.achilles_results’.

SQL:
select concept_hierarchy.concept_id,
isNull(concept_hierarchy.soc_concept_name,‘NA’) + ‘||’ + isNull(concept_hierarchy.hlgt_concept_name,‘NA’) + ‘||’ + isNull(concept_hierarchy.hlt_concept_name,‘NA’) + ‘||’ + isNull(concept_hierarchy.pt_concept_name,‘NA’) + ‘||’ + isNull(concept_hierarchy.snomed_concept_name,‘NA’) concept_path, ar1.count_value as num_persons,
round(1.0ar1.count_value / denom.count_value,5) as percent_persons,
round(1.0
ar2.count_value / ar1.count_value,5) as records_per_person
from (select cast(stratum_1 as int) stratum_1, count_value from shs_oklahoma.shs_oklahoma_achilles.achilles_results where analysis_id = 400 GROUP BY analysis_id, stratum_1, count_value) ar1
inner join
(select cast(stratum_1 as int) stratum_1, count_value from shs_oklahoma.shs_oklahoma_achilles.achilles_results where analysis_id = 401 GROUP BY analysis_id, stratum_1, count_value) 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 shs_oklahoma.shs_oklahoma.concept
where domain_id = ‘Condition’
) snomed
left join
(select c1.concept_id as snomed_concept_id, max(c2.concept_id) as pt_concept_id
from
shs_oklahoma.shs_oklahoma.concept c1
inner join
shs_oklahoma.shs_oklahoma.concept_ancestor ca1
on c1.concept_id = ca1.descendant_concept_id
and c1.domain_id = ‘Condition’
and ca1.min_levels_of_separation = 1
inner join
shs_oklahoma.shs_oklahoma.concept c2
on ca1.ancestor_concept_id = c2.concept_id
and c2.vocabulary_id = ‘MedDRA’
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
		shs_oklahoma.shs_oklahoma.concept c1
		inner join 
		shs_oklahoma.shs_oklahoma.concept_ancestor ca1
		on c1.concept_id = ca1.descendant_concept_id
		and c1.vocabulary_id = 'MedDRA'
		and ca1.min_levels_of_separation = 1
		inner join 
	  shs_oklahoma.shs_oklahoma.concept c2
		on ca1.ancestor_concept_id = c2.concept_id
		and c2.vocabulary_id = 'MedDRA'
		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
		shs_oklahoma.shs_oklahoma.concept c1
		inner join 
		shs_oklahoma.shs_oklahoma.concept_ancestor ca1
		on c1.concept_id = ca1.descendant_concept_id
		and c1.vocabulary_id = 'MedDRA'
		and ca1.min_levels_of_separation = 1
		inner join 
		shs_oklahoma.shs_oklahoma.concept c2
		on ca1.ancestor_concept_id = c2.concept_id
		and c2.vocabulary_id = 'MedDRA'
		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
		shs_oklahoma.shs_oklahoma.concept c1
		inner join 
		shs_oklahoma.shs_oklahoma.concept_ancestor ca1
		on c1.concept_id = ca1.descendant_concept_id
		and c1.vocabulary_id = 'MedDRA'
		and ca1.min_levels_of_separation = 1
		inner join 
		shs_oklahoma.shs_oklahoma.concept c2
		on ca1.ancestor_concept_id = c2.concept_id
		and c2.vocabulary_id = 'MedDRA'
		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 shs_oklahoma.shs_oklahoma.concept soc
	 on hlgt_to_soc.soc_concept_id = soc.concept_id
) concept_hierarchy on ar1.stratum_1 = concept_hierarchy.concept_id
, (select count_value from shs_oklahoma.shs_oklahoma_achilles.achilles_results where analysis_id = 1) denom

order by ar1.count_value desc

R version:
R version 3.4.4 (2018-03-15)

Platform:
x86_64-pc-linux-gnu

Attached base packages:

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

Other attached packages:

  • httr (1.4.1)
  • Achilles (1.6.7)
  • DatabaseConnector (2.4.2)

(Ajit Londhe) #3

I think the underlying error will be visible in the log file. Can you post that? You’ll find it in log_achilles.txt off of the outputFolder path.


(Kai Post) #4

|2020-02-26 23:40:39|[Main thread]|INFO|Achilles|achilles|[Main Analysis] [COMPLETE] 2201 (0.077646 secs)|
|2020-02-26 23:40:39|[Main thread]|FATAL|Achilles|FUN|Error in .mergeAchillesScratchTables(resultsTable = table, connectionDetails = connectionDetails, : unused argument (cdmVersionFolder = cdmVersionFolder) Calls: achilles -> lapply -> FUN |
|2020-02-26 23:40:40|[Main thread]|FATAL|DatabaseConnector|.createErrorReport|Error: Error executing SQL: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs_oklahoma.shs_oklahoma_achilles.achilles_results’. An error report has been created at /opt/app/errorReport.txt|


(Kai Post) #5

When should the achilles_results table be created? I have not been able to find any error messages regarding the failed creation of this table. I shouldn’t have to create this manually from what I understand …


(Ajit Londhe) #6

Correct, that table is created as the final step, where all the staging tables are merged into achilles results tables.

Are there any other errors in the log file? And did you run this with runCostAnalysis set to FALSE?


(Ajit Londhe) #7

Ahh, found the issue, there was a missing variable. Can you uninstall the package, restart your R session, then run

devtools::install_github("OHDSI/Achilles", ref = "cdm_v6_2020")


(Kai Post) #8

Wonderful! I will give that a try! Thank you so much.


(Kai Post) #9

That took care of the unused argument error message. Now, it looks like there is a problem with the SqlRenderer:

2020-02-28 00:30:04 [Main thread] INFO Achilles achilles [Main Analysis] [COMPLETE] 2201 (0.218173 secs)
2020-02-28 00:30:24 [Main thread] WARN SqlRender loadRenderTranslateSql file("") only supports open = “w+” and open = “w+b”: using the former
2020-02-28 00:30:24 [Main thread] FATAL SqlRender loadRenderTranslateSql Error in readChar(pathToSql, file.info(pathToSql)$size) : invalid ‘nchars’ argument Calls: achilles … .mergeAchillesScratchTables -> -> readChar
2020-02-28 00:30:25 [Main thread] FATAL DatabaseConnector .createErrorReport Error: Error executing SQL: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘shs_oklahoma.shs_oklahoma_achilles.achilles_results’. An error report has been created at /opt/app/errorReport.txt

(Ajit Londhe) #10

Ah, my mistake – the merge step is not specific to v5 or v6, it is the same script for both. Pushed a fix (tested on my local postgres). Can you re-install and re-try?


(Kai Post) #11

Thank you so much! After your last update it finally ran all the way. I will dive into the results now and let you know how it goes!
I did have to make a few bug fixes myself before you helped me with the CDM6 problems, mostly changes that had to be made to be able to use Achilles with MS SQL Server. I should probably report these to someone so it can be included in one of the next updates of the code base.


(Ajit Londhe) #12

Great!

Please report any issues with Achilles here: https://github.com/OHDSI/Achilles/issues


t