Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Iterators

Hello. I have a table with a single column: ID
It's the IDs of every file that has changed.
I'm iterating through another table for additional information about each file in that list.
Right now, I'm using an iterator to send one ID at a time to an RDS query component and running SQL: "select item_id,read_access from item_attributes
WHERE item_id = ${ITEM_ID}"

Is there an option or other component that I can use to group these IDs together so I can do something like:
"select item_id,read_access from item_attributes
WHERE item_id in (${ITEM_IDS})

3 Community Answers

Matillion Agent  

David Lipowitz —

Hi Dan,

Thanks for the question. You could use a Python Script component to execute a query with a LISTAGG function that concatenates the IDs into a comma-separated string. That value could be assigned to a variable and passed into your IN list in the manner you describe.

Please see the Python Script link above for details on using cursors in this manner as well as assign context variables.

Hope that helps and please let us know if you need more support on this.

Best Regards,
Dave


Dan Dow —

Hello Dave,

Thanks for the answer. That was going to be my next move. I was hoping there was some way to iter in chunks with a "native" Matillon iterator. Do you know if this is on the road map?

Thanks,

Dan


Matillion Agent  

David Lipowitz —

Hi Dan,

I’m not aware of any plans to chunk up Matillion iterators in the way that you describe. However, another option if you’re interested might be to use a Matillion transformation job to create a table containing the IN list of interest. Then you could loop over those results in a Table Iterator without having to call Python.

Hope that helps and makes sense; please let us know if you need more support on this approach.

Best Regards,
Dave

Post Your Community Answer

To add an answer please login