Error executing SQL: Incorrect syntax near the keyword 'SELECT'.

solis9753 commented 4 years ago

I am trying to run the package on a new database which uses 'sql server' for a dbms.

While trying to run the createEventPairs.R function, I get the following error:

Error: Error executing SQL: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'SELECT'. An error report has been created at Trajectories/errorReportSql.txt

errorReportSql.txt

1) I am not sure how to create a reproducible example with the specific dbms. 2) The whole function runs smoothly when using sqlite from within Eunomia package, which to me, means that the '1CohortCC.sql' is being read and executed just fine. I cannot comprehend why I get an error here.

Do you have any suggestions?

SulevR commented 4 years ago

@solis9753 What are your parameter values for "resultsSchema"?

I guess there is some slight problem with that. Perhaps add values of the following parameters also:

cdmDatabaseSchema
vocabDatabaseSchema
resultsSchema
sqlRole
prefixForResultTableNames
cohortTable

solis9753 commented 4 years ago

Hi @SulevR,

I have been looking into this issue and just verified it on a machine that runs on SQL Server DBMS.

The error is generated because the SQL scripts are being written in PostgresSQL syntax (right?). Apparently, SqlRender package has a standard language which is "OHDSI SQL", a subset of "SQL server" according to the Book of OHDSI and broadly one can just use "SQL Server" according to this, which seems much more recent resource.

Therefore the issue appears to be this; the standard syntax to use along with SqlRender package to translate to any other of the supported languages is "SQL Server".

solis9753 commented 4 years ago

So the problem with this issue is that the CREATE TABLE...AS... SELECT code appears only applicable in PostgresSQL and SQLite but not in any other SQL languages. See the errorReportSql.txt above

SulevR commented 4 years ago

It is true that we originally wrote the scripts in PostgreSQL, but we modified them for SQL Server for the reasons you mentioned and currently, SQL Server version is used. It is still possible that some queries are PostgreSQL-specific, but I think that this is not the case in this issue.

Google says that CREATE TABLE ... AS ... SELECT should be an allowed syntax for SQL Server.

What I see from the error report is that something seems incorrect with the schema and table names (too many dots here):

CREATE TABLE hds1.sioannou.gg_etcohort AS ....

That's why I'm asking you to give your values of the following parameters:

resultsSchema
cdmDatabaseSchema
vocabDatabaseSchema
resultsSchema
sqlRole
prefixForResultTableNames
cohortTable

SulevR commented 4 years ago

Well, now googling again - not sure anymore whether CREATE TABLE AS ... SELECT is allowed in SQL Server... Or should we replace them with SELECT ... INTO ... FROM ...

SulevR commented 4 years ago

After additional investigation it seems that @solis9753 is right - CREATE TABLE AS ... SELECT is not a correct syntax in SQL Server and we should replace them with something like this: https://ohdsi.github.io/TheBookOfOhdsi/Cohorts.html#finding-first-use

@Kadri0 Can you make the required modifications? Edit first table creation first and recheck that it is translated to PostgreSQL correctly.

Kadri0 commented 4 years ago

@solis9753 The SQL syntax has now been changed

EHDEN / Trajectories

Error executing SQL: Incorrect syntax near the keyword 'SELECT'. #5