I’ve been experimenting with running Achilles against a CDM stored in Oracle. For starters, I copied the Postgres database from the ohdsi-in-a-box into Oracle, and then attempted to run Achilles. The database connection succeeds, but when it gets to the queries that start to do the analysis, I get the error shown at the bottom of this message. I’ve double checked that all the tables copied from the VM. I’ve also run the Achilles with the sqlOnly=True flag set and the resulting output include a number of “CREATE TABLE” statements that, like the below error, include a null field which throws an error in Oracle, but is allowed in SQL Server and PostgreSQL.
I’ve typed the create table statement into the Shiny version of SqlRender, and it doesnt seem to translate into the syntax that Oracle prefers which would envelop the “null” field for Stratum_4 and Stratum_5 within a “cast” function that specifies the data type – just like it does for the the Stratum_1, 2 and 3 fields which have some non-null value.
Any ideas?
Thanks!
DBMS:
oracle
Error:
java.sql.SQLSyntaxErrorException: ORA-01723: zero-length columns are not allowed
SQL:
–HINT DISTRIBUTE_ON_KEY(stratum_1)
CREATE TABLE qea1l667s_tmpach_0
AS
SELECT
0 as analysis_id, CAST(‘OHDSI CDM V5 Database’ AS VARCHAR(255)) as stratum_1, CAST(‘1.6’ AS VARCHAR(255)) as stratum_2,
CAST(SYSDATE AS VARCHAR(255)) as stratum_3,
null as stratum_4, null as stratum_5,
COUNT(distinct person_id) as count_value
FROM
mark.PERSON
R version:
R version 3.5.1 (2018-07-02)
Platform:
x86_64-w64-mingw32
Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base
Other attached packages:
- Achilles (1.6)
- OhdsiRTools (1.5.5)
- httr (1.3.1)
- openxlsx (4.1.0)
- rjson (0.2.20)
- SqlRender (1.5.2)
- DatabaseConnector (2.2.0)
- rJava (0.9-10)
- devtools (1.13.6)