search

OHDSI / SqlRender

This is an R package and Java library for rendering parameterized SQL, and translating it to different SQL dialects.
https://ohdsi.github.io/SqlRender
Other
82 stars 77 forks source link

`missing right parenthesis` on Oracle 19 #326

Closed tlecarrour-ee closed 1 year ago

tlecarrour-ee commented 1 year ago

Hi,

Describe the bug Achilles analysis 2004 fails on Oracle 19 with message ORA-00907: missing right parenthesis.

To Reproduce Run the analysis on Oracle 19 using Achilles v1.7.0 and SqlRender v1.12.0. Following @fdefalco's advice on Achilles' repo, I tried updating to SqlRender v1.13.0, but the problem persists.

Possible fix Looks like the as command does not work as expected on Oracle 19. Removing them in some places fixes the problem. For instance:

(SELECT count(distinct(person_id)) as totalPersons FROM omop_cdm.person ) as totalPersonsDb   UNION ALL

As to be replaced with:

 (SELECT count(distinct(person_id)) as totalPersons FROM omop_cdm.person ) totalPersonsDb   UNION ALL
schuemie commented 1 year ago

I don't think I can parse the source query myself ;-) Why would you need to alias a subquery if you're going to union it?

Could you provide the whole source SQL, and the erroneous translated SQL?

tlecarrour-ee commented 1 year ago

Thanks for you reply! Unfortunately, I'm not a SQL expert and, as It's my first experience with Oracle —I usually use PostgreSQL—, I'm a bit puzzled by this.

I've uploaded the files to a public repo:

Hope this helps.