Use of "With Statement" subqueries

[AlleghenyAnalyst12]

I've seen this asked a couple of times, but not sure I've ever seen a definitive answer.

I have a number of involved SQL queries that utilizes the "WITH" Statement subquery format. Something like:

WITH TBL_A AS (
SELECT * FROM <SOMETHING> WHERE <CONDITION>),

TBL_B AS (SELECT * FROM TBL_A WHERE <CONDITION>)

SELECT * FROM TBL_B;

It would be nice to be able to just drop this code into an IN-DB connection, but it seems like Alteryx doesn't like the WITH statement, because on the backend it's basically creating WITH statements whenever it is doing the data manipulation. I was informed to nose around the Dynamic In-DB tool, and indeed you can see the SQL statements it generates do use the WITH statement, and I got the impression that I can somehow use these dynamic tools to be able to incorporate my WITH statement queries. Currently I'm re-writing everything in Alteryx which is a pain since some things are just easier in SQL - not to mention I already have the scripts. Thanks for the help. 

[BrandonB]

The "WITH" statements should work just fine in a regular Input Data tool if that is an option for you. I have seen some cases where you can wrap your with statement in an In-DB tool with a Select * and put the WITH query in parenthesis and get it to work In-DB. May be worth testing out to see if that allows you to run it.

[BrandonB]

See these links for more information: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/In-DB-tools-add-a-SELECT-statement-...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/common-table-expressions-in-database-w...

[gregx]

You haven't seen a definitive answer and you won't.

Alteryx engine sucks. I've been using this tools for years and the team hasn't done anything to fix basic things.

[BrandonB]

Hi @gregx, my first response above where I recommended wrapping it with a SELECT is a valid solution in getting most of these statements to work. There have been massive strides in both product improvement and bug fixes over the years, so please continue to leverage our support page (https://community.alteryx.com/t5/Support/bd-p/SupportPage) and our ideas page (https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas) so that we can make the platform even better! I can reassure you that while the product and engineering teams can't get to every single idea, they are certainly trying to prioritize and make the biggest impact to Alteryx customers. 

[gregx]

"getting most of these statements to work" - what a professional approach... sometimes it works, sometimes not.

CTE is very important and basic solution for sourcing data, I can't imagine how could you release a product that doesn't handle it.

I think the idea of working CTE statements has been with you for few years and nothing has been done.

Also, this is neither an 'extra' solution, not a new functionality, but a something basic that must be fixed.

[simonaubert_bd]

Hello @AlleghenyAnalyst12 @gregx @BrandonB 

There is this idea with more than 40 votes..
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/In-DB-to-Allow-Common-Table-Expressi...

Maybe it's time to think otherwise?

[jdminton]

I think this would be a good addition also, but I do know that sometimes programming changes like this can be cumbersome and problematic in other areas. The cost and risk may be fairly excessive for a set of tools that are not used by a significant percentage of users.

I'm wondering if something like a "run query" tool for in-DB that kicks off a saved SQL query and allows output to be saved to temporary tables would be more useful and cover the requirements of the CTE. At least as a workaround, this would give users a way to run complex SQL queries without having to diagnose issues with Alteryx conversions.

[simonaubert_bd]

@jdminton1/ there is also an idea for that ;)
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/SQL-Editor-Tool-for-In-DB-Workflow/i...

2/On all my Alteryx projects, there is in database and this the default, you only go in-memory if you have no choices. And I speak of dozens or hundred users on each projets. So, I wouldn't say it's rarely used. The amount of ideas or questions on the topic says otherwise too.

[jdminton]

The topic you linked is not what my suggestion was. I was speaking of purely running a query that is saved elsewhere as SQL code. 

Also, I didn’t say that in-DB was “rarely used”. I use it, but based on statistics shared at Inspire and my experience with other users, there is not a significant percentage of users who interact with in-DB tools on a regular basis.