SQLSyntaxErrorException: ORA-00905: missing keyword

[scossin]

Describe the bug We encountered a SQLSyntaxErrorException: ORA-00905: missing keyword error with the following queries: 430, 630, 730, 830, 1830, 2130. This error occurs because the template query is not correctly translated to Oracle DBMS.

To Reproduce

cat(SqlRender::translate(
  sql = SqlRender::readSql(file.path(system.file(package = "CatalogueExport"), "sql/sql_server/analyses", "430.sql")),
  targetDialect = 'oracle')
)

The SQL returned still contains "into @scratchDatabaseSchema@schemaDelim@tempAchillesPrefix_430" which causes the error.

By adding a ";" at the end of the SQL query template, it solved the problem. The "into ..." line was replaced by a "CREATE TABLE @scratchDatabaseSchema@schemaDelim@tempAchillesPrefix_430"

sql <- "-- 430  Number of descendant condition occurrence records,by condition_concept_id
--HINT DISTRIBUTE_ON_KEY(stratum_1)

WITH CTE_CONDITION AS (
    SELECT ca.ANCESTOR_CONCEPT_ID AS CONCEPT_ID, COUNT_BIG(*)
 AS DRC
    FROM @cdmDatabaseSchema.CONDITION_OCCURRENCE co
        JOIN @cdmDatabaseSchema.CONCEPT_ANCESTOR ca
            ON ca.DESCENDANT_CONCEPT_ID = co.CONDITION_CONCEPT_ID
    GROUP BY ca.ANCESTOR_CONCEPT_ID
)
SELECT  430 as analysis_id,
  CAST(co.CONDITION_CONCEPT_ID AS VARCHAR(255)) AS stratum_1,
  cast(null as varchar(255)) AS stratum_2,
  cast(null as varchar(255)) as stratum_3,
  cast(null as varchar(255)) as stratum_4,
  cast(null as varchar(255)) as stratum_5,
  floor((c.DRC+99)/100)*100 as count_value
into @scratchDatabaseSchema@schemaDelim@tempAchillesPrefix_430
FROM @cdmDatabaseSchema.CONDITION_OCCURRENCE co
    JOIN CTE_CONDITION c
        ON c.CONCEPT_ID = co.CONDITION_CONCEPT_ID
GROUP BY co.CONDITION_CONCEPT_ID, c.DRC
; "
cat(SqlRender::translate(sql, targetDialect = 'oracle'))

I don't know if fixing this bug for Oracle could cause another bug for other DBMS.

[PRijnbeek]

Yes that could indeed be the case thanks, this ; is needed for SQLRender.

it will not be an issue for any other DBs

Thanks unfortunately we cannot test the package on Oracle ourselves.

[PRijnbeek]

We will close them when they are in master