Use of "With Statement" subqueries

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.

Workflow

Common Use Cases

In Database

In-Database

Accepted answers

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.

All comments

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-to-Common-Table-Expression/ta-p/593797

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/common-table-expressions-in-database-workflows/td-p/30990

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

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-Expressions-in-Custom-SQL/idi-p/176518

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/idi-p/366086

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.

simonaubert_bd

@jdmintonOh, yes, I misunderstood the elsewhere. Something more like a stored procedure or view? You can already create a view that use a CTE. And then use the view in connect in db or input tool.

For in-db tools, I guess it depends of the adressed market. In France, I can assure you it's widely used on all the big projects because that's something the vendors insist on. On my actual project, on 170 users, something like 150 use in-db tools. On my previous project, that was 70 on 70. Etc, etc. (Of course, I don't know all the projects in France but I have contacts on all the big ones and on these, whereas the vendor is my employer or another vendor, in-db is a huge feature... it's a small world ^^... and we're not present as Inspire because it's too far for us ). I would explain that by two reaons : Alteryx entered the french market only two years before the in-db, so there were only a few projects where the vendor didn't mention in-db... also, with lower salaries, we sell less than in the US, more focused on data-intensive projects to keep a good ROI. But I have to admit I have no idea of the US market, I only know that Alteryx sells a lot to accounting companies, which is not the case here (mostly bank and insurances).

Best regards,

Simon

Jason_

I had a similar issue today with the regular input tool. The method suggested by @BrandonB worked perfectly

Original statement that was not working:
WITH x AS ([SELECT CLAUSE]),
y AS ([SELECT CLAUSE])
SELECT <fields> FROM x INNER JOIN y on x.PK = y.FK

New statement that resolved the issue:
SELECT * FROM

(

WITH x AS ([SELECT CLAUSE]),
y AS ([SELECT CLAUSE])
SELECT <fields> FROM x INNER JOIN y on x.PK = y.FK

)

simonaubert_bd

Hello @Jason_

It depends of your database. If you're lucky enough, nesting of CTE is supported.

But in Hive it wasn't (still not ?) : https://community.cloudera.com/t5/Support-Questions/Usage-of-with-clause-in-hive/m-p/289960

Best regards,

Simon

latimo1

I just tried this and still get the With error. I am getting the query from ThoughtSpot, connected to Snowflake.

apathetichell

@latimo1 - if you are using a WITH statement with Snowflake - try input data/on canvas tools instead of In-DB tools.

latimo1

I ended up using AIM Chat, our AI app to rewrite the query without With. Thanks for all the suggestions!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703