We’re trying to load data from a MS SQL database. Most queries work fine, but when we use a simple query using the WITH keyword, we get an error “Incorrect syntax near the keyword WITH”.
An example query (which is actually data agnostic):
WITH sometable (A) AS
(SELECT GETDATE() as A)
SELECT A
FROM sometable;
We’ve already done some research, but unfortunately all mentioned stackoverflow suggestions don’t solve our problem. We’re suspecting that underwater Quicksight changes to the query are the culprit, so I was wondering if someone else ran into the same problem and found a solution?
Yes we checked, we’re running version 15. The mentioned query does work when we run we directly run it on MSSQL, but not when we run it via Quicksight.
Thanks for mentioning this solution. Using views is a bit of a last resort for us, as that requires us to create more logic in the underlying MSSQL database, whereas we would like to manage that in Quicksight (among others due to governance).
Ideally we would like to understand why the WITH statement doesn’t work in Quicksight, and possibly what we can do to resolve it. I think we would already be helped if we could see what the actually query is Quicksight makes out of our original query, if there is a way to see that.
The database engine referred here is Microsoft SQL Server, not MySQL, just to clarify since some posts here refer to MySQL only. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!
To help you better, it might be useful to share more details about your use case, such as the query you’re trying to run and any error messages you’re encountering. This information can be valuable for community members or Amazon QuickSight support to provide more targeted assistance.
