Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

In-DB to Allow Common Table Expressions in Custom SQL

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!

10 Comments
goodej
6 - Meteoroid

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
8 - Asteroid

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
5 - Atom

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

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes
 
AdrianaCortesB_7
5 - Atom

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
8 - Asteroid

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

i_love_databases
8 - Asteroid

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

But frankly, it's painful and requires some permissions

Mychele
7 - Meteor

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

aurian_g
5 - Atom

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
13 - Pulsar

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