Custom SQL queries with Common Table Expressions

Question & Answer
1

Are custom SQL queries with Common Table Expressions supported in QuickSight? I’m trying to write a recursive query but I’m getting an error.

2

If the datasource supports Common Table Expressions with recursive queries then you can use this in custom sql when creating datasets. For example, Athena does not support recursive queries via common table expressions but postgresql supports it.

1 Like
3

Generally, I would answer “no” – the best way to think of the Custom SQL is that it executes a sub-query of whatever your SQL is. If you’re looking at your database engine you’ll see a transaction that looks like this:

Select * FROM (
[[Your Query Here]]
) qs

I’d love to see them improve that in such a way that we could execute stored procedures so we could optimize code, but the workarounds we’ve tried are Views or Table-Valued Functions (don’t love either…) or more commonly scheduling a job to create/update/append a table so we can just select * from table

Hope that helps!

1 Like
4

Thanks! That makes sense.