Dynamically change queries in In database filters

[Alayna]

Hi, I'm setting up a workflow that connects to an Oracle Peoplesoft database where I'm using three different tables. I have two filters:

one to change the date: "Pay_End_Date" between to_date('01-01-2021', 'YYYY-MM-DD') and to_date('02-01-2021', 'YYYY-MM-DD') 

and another to change a paygroup: "AAA"

An input from an excel sheet has the name of the paygroup and a performance date range. Ideally, I want Alteryx to be able to read the columns from the Excel file and change the filters automatically, instead of me manually changing them. I saw the data stream in too, but that requires a write connection to Oracle and I'm sure my admin will not allow that. Any other workarounds?

[BrandonB · Accepted solution]

The trick here will be to use the Dynamic Input tool. You will want to use the ability to change the SQL WHERE clause to swap the values with whatever column values are coming into the tool. Alternatively you could build your query with a formula tool and add the dates/paygroups accordingly which would then feed into the Dynamic Input tool and dynamically query the tables using the query created from the previous formula tool. 

[Alayna]

Hi @BrandonB thanks for the reply, sorry for the late response. I definitely think the dynamic input tool will help but I'm still confused on how I would connect it to the in database tool? I see the option to edit queries but I can't find examples on how it actually edits in a database

[Alayna]

Actually never mind, I did not realize there was a dynamic in db tool. Thanks again!