In-DB to Allow Common Table Expressions in Custom SQL

[OneandOnly13]

I would like to suggest creating a fix to allow In-DB Connect tool's custom SQL to read Common Table Expressions. As of 2018.2, the SQL fails due to the fact that In-DB tools wrap everything in a select * statement. Since CTE's need to start with With, this causes the SQL to error out. This would be a huge help instead of having to write nested sub selects in a long, complex SQL code!

[goodej]

I would like to second the recommendation above and expand upon it as well.  Common table expressions help in keeping the code readable, are pretty basic, and would be helpful if they were supported.

Additionally, the SQL Editor "Test Query" feature in the query builder should not provide a "Test Query Success" message if in fact the query is not going to run.  I'd like to see the SQL Editor working as raw code (i.e. if I test and run some SQL code in SQL SSMS then when I copy and past it into the SQL Editor it should still work).

Last quirk I wish would change about the Query view is the formatting.  I past nicely formatted SQL code into the editor and it gets squashed together into an unreadable mess.  The next time I need to review what a query is doing, it takes more effort than it really should.

[justin_winter]

Just a tip, if you wrap your CTE with “Select * from( ***CTE SQL***)” replacing ***CTE SQL*** with your CTE script then it seems to pretty much work fine with In DB. Given that, however, it seems that it would be easy for Alteryx to do that themselves if a CTE was entered, but maybe it varies heavily across database types? I tried that SQL in both Athena and Redshift and tried the In-DB logic on Redshift and it all worked.

[md000]

thank-you @justin_winter.  This is excellent. Works for snowflake as well.  This tip needs to be more widely publicised !

[AdrianaCortesB_7]

I agree this would be super helpful. I find really time consuming trying to replicate the logic with alteryx tools once is already built into CTEs. I think this would be a huge improvement and would really help my work.

[gregx]

5 years and no solution yet? it's a bad joke

[i_love_databases]

A workaround would be to create previously a view with the cte...

But frankly, it's painful and requires some permissions

[Mychele]

The trick to wrap the SQL with a select statement doesn't always work.

[aurian_g]

This has really hamstrung my ability to build systems based off of complex SQL queries requiring CTE's for readability, organization, scalability and reproducibility. You can't simply just wrap a select * when using multiple with statements and joins (as some users suggested). My use case is we are using the "Block Until Done" tool where we need a process to run PRIOR to our query being executed. The input tool doesn't allow you capability to leverage "Block Until Done" because it doesn't accept an incoming connection. Which is why we use "Dynamic Input In-DB" connections that allows us to leverage the "Formula" tool which can be connected as the 2nd component of the "Block Until Done" tool. Re-writing complex SQL queries into messy subqueries is not practical nor scalable. 

[simonaubert_bd]

Hello @aurian_g If you want to pass some SQL before your workflow, maybe you would be interested in this idea ;)
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Pre-SQL-for-InDB/idi-p/94203

(that said : in db ideas all needs some tractions if we want to motivate Alteryx to improve those features ;) )

Best regards,

Simon