Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

common table expressions, in-database workflows

jdubw
5 - Atom

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 

7 REPLIES 7
HenrietteH
Alteryx
Alteryx

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.

 

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

davidhenington
10 - Fireball

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. 

davidhenington
10 - Fireball

Apparently not. Seems like a good idea to remove the option from the connect in-db tool in order to avoid confusion. 

jdubw
5 - Atom

Yeah, have not been able to get CTEs to work for in-DB workflows.  Sometimes can re-write the SQL to use subqueries versus CTEs.  Some of the data I deal with is in a set of source-app legacy tables in DB2 in a mainframe environment.  Have to be super careful of SQL performance there.   

HenrietteH
Alteryx
Alteryx

Hi @davidhenington

I just looked in the Visual Query builder (briefly) and couldn't find an option to build a WITH CTE statement in there. Can you share a screenshot of where you are seeing this? 

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

William1
5 - Atom

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

HenrietteH
Alteryx
Alteryx

Hi @William1

WITH CTE is not supported for the dynamic input in-db tool. Feel free to submit an idea with your business case on our forum though.

 

You could use a macro to edit the query in a regular input tool. In it's simplest form, that would look something like this: 

2-19-2018 11-13-12 AM.png

 

 

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

Labels