SQL query giving error "Invalid SQL statement. Expected a single SQL SELECT statement."

Hello everyone, I am trying to combine two datasets - coming from the same SQL table - with different frequencies (monthly and annual) via the following SQL query:

cnxn = database('MySQL ODBC', '', '');
query = [
    'WITH Table1 as ( ' ...
    'SELECT obs_date, obs_value as curr ' ...
    'FROM mydatabase.table ' ...
    'WHERE frequency = ''Q'' '... 
    'ORDER BY obs_date ASC ), ' ...
    'Table2 as ( ' ... 
    'SELECT obs_date, obs_value as prev ' ... 
    'FROM mydatabase.table ' ... 
    'WHERE frequency = ''A'' ' ... 
    'ORDER BY obs_date ASC )  ' ... 
    'SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.obs_date = Table2.obs_date '];
data=select(cnxn,query);

The query produces the error message

Error using database.odbc.connection/select (line 78)
Invalid SQL statement. Expected a single SQL SELECT statement.

Initially I thought the error was due to the presence of a semicolon, as suggested in this thread. The semicolon now is removed, but the query still doesn't work.

The same query works totally fine in Hive, Impala, RStudio and Stata.

Would you have an idea of what can cause the error?

[Trevor]

I'm having the same problem - queries with CTEs don't seem to do well in R2022a (they worked fine in previous versions of MATLAB).

The problem appears to be replicated for both:

• ODBC and JDBC connections (your example appears to use OBDC; I'm using JDBC).
• MySQL and Snowflake (your example appears to use MySQL; I'm using Snowflake).

One bandaid fix is to just wrap the query in a single SELECT statement:

data = select(cnxn, ['SELECT * FROM (' query ')']);

But this solution is unsatisfactory in the long run. Will keep searching for a solution that doesn't require this type of workaround.

Answer 1

[Pratik]

Hi Sofia,

As per my understanding, you are attempting to merge two datasets using the SQL query provided, which uses the "SELECT" statement. However, the query is resulting in an "Invalid SQL statement" error.

The "select" method requires the query to be a simple “SELECT” SQL statement. MATLAB verifies the query text to confirm that it matches the anticipated pattern. Thus, starting the query with the "WITH" keyword instead of "SELECT" disrupts this pattern, resulting in an error.

To resolve this, “fetch” method can be used, which allows the inclusion of the “WITH” keyword in the query.

Please refer to the following documentation to read more about the alternative functionality:

https://www.mathworks.com/help/database/ug/database.odbc.connection.select.html#bvlvx8i-2

Hope this helps!