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. 

[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

[Jason_]

---

@simonaubert_bd wrote:

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

---

Agree. My Snowflake WITH clauses run just fine. I see the issue specifically with Oracle using an OCI connection.