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

18 次查看（过去 30 天）
Sofia Gori
Sofia Gori 2022-2-19
回答： Pratik 2023-12-4
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?
  1 个评论
Trevor
Trevor 2022-8-4
编辑：Trevor 2022-8-4
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Pratik
Pratik 2023-12-4
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!

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by