common table expressions, in-database workflows

I have a workflow where I have brought in 27k unique transactions into a temp table. I want to use this table as a common table expression to query another table on the databdase. The downstream table has too many records and is a source system for me to do a join, a CTE (or equivalent) would let me pull from the source only matching records for my source set of 27k unique keys. I don't know a whole lot about the In-Database workflow items, does someone know how to do this?

thanks much

Database Connection

Accepted answers

hhaigh1

Hi jdubw,

If you already have the query built that pulls your 27K reocrds, the easiest way to transfer it into Alteryx would be to use the WITH CTE statement in the regular input tool as you would in SQL. The regular input tool does allow a WITH CTE statement which you can enter under the SQL Editor tab when you chose/edit your table or query.

WITH CTE is not supported in the In-DB tools. If you wanted to use In-DB, you would have to run the SELECT portion of your WITH statement in the Connect In-DB tool and then use a Join In-DB tool to join to your other data set. All of that processing would happen on the database side, so it shouldn't be a problem with the number of rows you have on the other table. Then you could use the Data Stream Out tool to bring the data into Alteryx.

We always encourage users to submit an idea on our ideas page if they come across something that is currently not supported, so feel free to suggest support for WITH CTE here: http://community.alteryx.com/t5/Alteryx-Product-Ideas/idb-p/product-ideas

For an introduction to our In-DB tools, there is also a recording on the Virtual Training page.

All comments

hhaigh1

Hi jdubw,

For an introduction to our In-DB tools, there is also a recording on the Virtual Training page.

davidhenington

Hi, could be wrong but it appears as if in-db supports CTE's now? I see the option to add CTE in the visual query builder within connect in-db.

William1

Hi there,

It is very good how you welcome new ideas.

I need to combine a CTE for building of a hierarchy with mother-child relationships, and I need to pass a date in as a parameter so that I can get the hierarchy for whatever date. I have written the SQL and it works in a SQL Server Management Studie, however, Alteryx seems to struggle and if I have read your message correctly Alteryx doesn't support it. I would love to use CTEs in the "Dynamic Input In-DB" where the actual string with the query comes from a "Formula" (or another tool).

If an alternative is possible I would love to hear about it.

Kind regards,

William

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703