Join an Oracle Query to a local table

[pliskers]

I'm using Alteryx to query our data warehouse via an Oracle connection.  One of the tables I'm querying contains customer codes and I want to save time by joining directly to that table a list of about 9,000 accounts.  Currently, my workflow is downloading details for the entire customer universe and then filtering those results against my customer list.  I know there must be a faster way to apply that filter directly against the data warehouse table, but am not very familiar with SQL and don't know where or how that join could be created via my Alteryx workflow.

Is this possible via the SQL editor built into Alteryx?

THANKS!

[apathetichell]

yes. via dynamic input in-db. You can create a dynamic where clause and then match that against your larger database.

[pliskers]

Thanks, but I'm having a little trouble reading from your image.

My connection to the data warehouse is a query that's inserted as an Input to the workflow.  For simplicity's sake, let's say the field I want to join is named the same in the warehouse ("OUTLET") as in my table, which is just a flat file.

It looks like you're putting the WHERE clause into a formula tool and then merging (not joining) against the SQL query as an Append.  Is that correct? 

I'm already joining the SQL query to my table - but what I'm hoping is that I can run the SQL against this list before every outlet has to be queried on the server, rather than taking all that output and then applying the list filter.  It would greatly reduce the run time if the SQL ran with that list part of the initial query.  Would your suggestion accomplish that?

[apathetichell · Accepted solution]

O.k. so the first DB and the append is to get the results/parameter values that you want  -it basically generates your input. In this case it's from a database but that's secondary to your specific use case.

Once you have the results- you can summarize and append and then create your new query. new query is run against big database using in-db.

[pliskers · Accepted solution]

Thank you!