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

[VJ_88]

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

1. 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.
2. which is then compared with master list to check for new and old, New not present in master and Old - already present in master.
3. 2nd container has an input as SQL query oracle database
4. 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.
   1. we have 02 queries as union
   2. New ID are to be passed, and data are to be fetched for 06 months
   3. 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

[jrlindem]

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

[dreldrel]

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

[apathetichell]

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.