Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

2022-05-24 Updates: Login: If you are facing issues with logging in, please try clearing all your cookies or trying another browser. We have a fix being deployed on the evening of 5/25. Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.

Alteryx Designer Ideas

Share your Designer 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!

3 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 !