Pass ID's from Output of Container 1 as comma separated value in input of SQL query

Question

Pass IDs from Output of Container 1 as comma separated value in input of SQL query of Container 2.

* We have a control container in Alteryx 2024 which prepares a list if EMP_ID as output after all transformation which can be stored as Alteryx DB, excel, etc.
* which is then compared with master list to check for new and old, New not present in master and Old - already present in master.
* 2nd container has an input as SQL query oracle database
* we need to pass the output EMP_ID as comma separate value obtained in point 1 as input in where clause of SQL query of input of Container 2.* we have 02 queries as union
* New ID are to be passed, and data are to be fetched for 06 months
* for old id data needs to be fetched for 01 months

select * from table_A
where ID in (new id's separated by comma)
and month_id between 202503 and 202509
UNION
select * from Table_A
where ID in (old id's separated by comma)
and month_id between 202509

d.  month is already dynamic as derived from max date of TAble.
I need to figure out a way to pass id' dynamically from container 1

apathetichell · Answer

Your usage of containers and view of this as a containers issue  is abstracting  the problem. Your core workflow:

part 1---> retrieve ids.

part 2---> send ids in where clause.

For part 2 there are multiple ways to do this usually the easiest solution is to have them as a specific column ---> use a summarize tool in concat mode to create an "{element1}","{element2}"..."{elementn}" format --- use a formula tool to add this a field to your full query. use dynamic input (or dynamic input in-db) to run your query.

part 2 can be in a standard canvas. it can be in a container. It can be in a macro ---> whichever way you are most comfortable.

dreldrel · Answer

Just a random idea, might be similar to @jrlindem - maybe you can just use a few formula tools with summarise tools to build your final SQL query? Then just pass the final query to a dynamic input tool to query data from your database. So you may dont need to use control containers~ Or alternatively, you can split the processes into two workflows, where you'd like to have process control and logs. I've done this several times, hope this helps

jrlindem · Answer

I haven't had a need to do this, personally, but i've read up on it a bunch.  You can take a value.. in your case the output file name or specific value from the first container and pass that single value into the second container (to retain sequencing) and use a Dynamic Input tool to place that value into your SQL code in the exact spot you need it.

This knowledge article might help too:  Modifying SQL Query using the Dynamic Input Tool:

Hope that helps, -Jay