Oracle SQL statement bug running Achilles for the first time

Implementers
1

I am running Achilles the first time to generate an Achilles analysis. It successfully generates the first part (Achilles results) but I get a SQL error when it is working on the Achilles Heel results.

Error:
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

SQL:
insert into results.ACHILLES_results_derived (statistic_value,measure_id)
SELECT CAST(100.0*count_value/statistic.total_pts AS FLOAT) as statistic_value,
CAST(CONCAT(‘ach_’, CONCAT(CAST(analysis_id as VARCHAR(10)), ‘:Percentage’)) AS VARCHAR(100)) as measure_id
FROM results.achilles_results
cross join (SELECT count_value as total_pts FROM results.achilles_results r WHERE analysis_id =1 )

as

statistic
WHERE analysis_id in (2000,2001,2002,2003)

The error is the italicised as in the above statement. This is illegal in Oracle.

How can I change the sql that will be generated?

Is there a database setting I can use to make Oracle ignore the as?

2

Did you run the SQL through SQLRender before running it?

3

This is generated SQL which comes from Achilles.

4

This is generated SQL which comes from Achilles.

Regardless, you should be able to use SqlRender to make it work on Oracle, no?

http://ohdsi.github.io/SqlRender/articles/UsingSqlRender.html#table-aliases-and-the-as-keyword

EDIT: I see that SqlRender doesn’t allow you to do this. That said, you should be able to edit the SQL by navigating to the file in the package. On my machine the script is in \win-library\3.5\Achilles\sql\sql_server.