Create CTE in Quicksight - MS SQL Datasource

Question & Answer
, , , ,
1

I am trying to create a ‘Direct Query’ dataset in Quicksight against an MS SQL datasource.

I have tried making my test query as minimal as possible to rule out query issues. When I run this query directly within SQL Server Management Studio, it works without error.

The query I am entering into the Quicksight Dataset creator is

WITH [TestCTE] as (
select [ClientID] from TestingTwo)
select ClientID from [TestCTE]

When I click Apply, i get an error stating ‘Incorrect Syntax near WITH’. I have tried prefixing with a semicolon, which just changes the error message to ‘Incorrect Syntax near ;’

I ran SQL Profiler to see what was actually hitting the database, and found that QS is wrapping my query in an additional query.

/* QuickSight /
SELECT ds. FROM ( WITH [TestCTE] as (
select [ClientID] from TestingTwo)
select ClientID from [TestCTE] ) ds where 1 = 2

How do I get around this or how do I sucessfully create a CTE dataset using MS SQL in Quicksight?

2

The issue you’re facing is likely due to the way QuickSight handles SQL queries. QuickSight’s Direct Query feature doesn’t fully support the use of CTEs (Common Table Expressions) in the same way as running the query directly in SQL Server Management Studio.

To work around this, you can try the following:

  1. Embed the CTE directly in the main query:

    SELECT [ClientID] 
FROM (
    SELECT [ClientID] 
    FROM TestingTwo
) AS [TestCTE]

    This approach embeds the CTE logic directly in the main query, which should work with QuickSight’s Direct Query feature.

  2. Use a Derived Table instead of a CTE:

    SELECT [ClientID] 
FROM (
    SELECT [ClientID] 
    FROM TestingTwo
) AS [TestCTE]

    Derived tables are often more compatible with various BI tools, as they don’t rely on the CTE syntax.

  3. Create a View in the database and use that in QuickSight:

    • Create a view in your SQL Server database that encapsulates the CTE logic:
      CREATE VIEW [TestingTwoView] AS
SELECT [ClientID] 
FROM TestingTwo
    • Then, in QuickSight, you can use this view as the data source for your Direct Query dataset.

These approaches should help you overcome the CTE-related issue you’re encountering with QuickSight’s Direct Query feature. If you continue to face problems, you may want to consult the QuickSight documentation or reach out to AWS Support for further guidance.

2 Likes
3

Hi @murili - Thanks. can we logged it as a feature request as most of the cases users tested the sql in a client tool and expecting it should work in direct query within QS. This will help in expediting the development process.

Regards - Sanjeeb

2 Likes
4

We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)