Hi folks,
We initially had the Achilles application working against our system by populating just the person and observation period tables. As part of our ‘expansion’, we recently populated the condition_occurance table as well.
However, now we seem to be running into issues in generating the json files for the Achilles application.
I run the following commands which execute successfully.
connectionDetails ← createConnectionDetails(dbms=“postgresql”, server=“localhost/demo”, port=“5432”, user=“snkasthu”, password=“root”, schema=“public”)
achillesResults ← achilles (connectionDetails, cdmDatabaseSchema=“public”, resultsDatabaseSchema=“public”, sourceName="", smallcellcount=1, cdmVersion=“5”)
However, the following runs into problems.
exportToJson(connectionDetails=connectionDetails, cdmDatabaseSchema=“public”, resultsDatabaseSchema=“public”, cdmVersion=“5”, outputPath="/Users/snkasthu/Downloads/AchillesWeb-master/data/SAMPLE")
I get the following errors, which seem to indicate some sort of casting issue? 
Connecting using PostgreSQL driver
Generating condition treemap
|======================================================================| 100%
Generating condition reports
| | 0%Error executing SQL: Error in rJava::.jcall(s, “Ljava/sql/ResultSet;”, “executeQuery”, as.character(query)[1]): org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying * integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 92
An error report has been created at /Users/snkasthu/errorReport.txt
Error in value[3L] : no loop for break/next, jumping to top level
errorReport.txt reads as follows:
DBMS:
postgresql
Error:
org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying * integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 92
SQL:
SELECT c1.concept_id AS concept_id,
c1.concept_name as concept_name,
cast(num_stratum_4 * 10 AS VARCHAR) || ‘-’ || cast((num_stratum_4 + 1) * 10 - 1 AS VARCHAR) AS trellis_name, --age decile
c2.concept_name AS series_name, --gender
num_stratum_2 AS x_calendar_year, – calendar year, note, there could be blanks
ROUND(CAST(1000 * (1.0 * num_count_value / denom_count_value) AS NUMERIC), 5) AS y_prevalence_1000pp --prevalence, per 1000 persons
FROM (
SELECT num.stratum_1 AS num_stratum_1,
num.stratum_2 AS num_stratum_2,
num.stratum_3 AS num_stratum_3,
num.stratum_4 AS num_stratum_4,
num.count_value AS num_count_value,
denom.count_value AS denom_count_value
FROM (
SELECT *
FROM ACHILLES_results
WHERE analysis_id = 404
AND stratum_3 IN (‘8507’, ‘8532’)
) num
INNER JOIN (
SELECT *
FROM ACHILLES_results
WHERE analysis_id = 116
AND stratum_2 IN (‘8507’, ‘8532’)
) denom
ON num.stratum_2 = denom.stratum_1
AND num.stratum_3 = denom.stratum_2
AND num.stratum_4 = denom.stratum_3
) tmp
INNER JOIN public.concept c1
ON num_stratum_1 = CAST(c1.concept_id as VARCHAR)
INNER JOIN public.concept c2
ON num_stratum_3 = CAST(c2.concept_id as VARCHAR)
ORDER BY c1.concept_id,
num_stratum_2
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)
- DatabaseConnector (1.3.0)
- RJDBC (0.2-5)
- rJava (0.9-7)
- DBI (0.3.1)
- SqlRender (1.1.2)
