multi-step or nested queries (in R RMySQL or RSQLite)?

Question

after getting some feedback to my original question, I have edited this question to include additional detail and to be more specific about what I am still stuck on...

I am using 'R' version 4.3.2 to run a script (text file named ExportUtility.R) that allows me to export data from a database into a .CSV file. I run this export utility from within a .BAT file with the following command...

"C:\Program Files\R\R-4.3.2\bin\x64\r.exe" CMD BATCH ExportUtility.R

The key parts of the the ExportUtility.R field seem to be the following: First, establishing a connection to an ODBC data source...

source_conn <- DBI::dbConnect(odbc(), dsn="LabelTraxx_64_Lum")

Next, executing the query (where I can set sql_query to whatever SQL I want...

data <- dbGetQuery(source_conn, sql_query)

Next, writing the results of the query to a .CSV file (where I can set filename to whatever I want)...

write.table(data, file=filename, append=FALSE, quote=TRUE, sep=",", col.names=TRUE, row.names=FALSE)

The exact SQL Query I am having trouble with is as follows:

WITH T1 AS (SELECT Number, OrderDate FROM Ticket) SELECT Number, OrderDate FROM T1

When I try to run this query, I get the following error..

Error: nanodbc/nanodbc.cpp:1769: 08004: Server rejected the connection:
Failed to parse statement.
 
<SQL> 'WITH T1 AS (SELECT Number, OrderDate FROM Ticket) SELECT T1.Number, T1.OrderDate FROM T1 '
Execution halted

When I run just the "Inner" query below, I get no error, and I get the expected data in my .csv file.

SELECT Number, OrderDate FROM Ticket

If anyone can help me figure out what is wrong with the query in which I use the WITH clause, I would greatly appreciate it.

Thanks in advance, Paul

Answer 1

CTE (common table expression), perhaps?

with StepOneResults as (
  SELECT Table1.GroupField, Count(Table1.CountField)
  FROM Tabel1
  GROUP BY Table1.GroupField
)
SELECT StepOneResults.GroupField, Table2.SomeOtherField
FROM StepOneResults
  LEFT JOIN Table2 ON Table2.GroupField = StepOneResults.GroupField

Answer 2

It looks to me like your SQL CTE is not well formed - the final element is simply:

OrderDate FROM T1

You could try changing it to:

SELECT OrderDate FROM T1

If that doesn't solve your problem, you might find it easier to debug your SQL if you took it out of your R code and put it in it's own file. Most editors recognize SQL and highlight the syntax etc. The sqlhelper package is designed to execute SQL written this way (full disclosure: I wrote it).