Question Using loop in a process

Hi,

I have a simple process :
1. SQLOperation which is a simple select on one column
2. A mapping

I want to set a variable with the result of of the SQLOperation and iterate the mapping on each value of this variable.

The thing is we can't link a SQLOperation to a mapping with a direct bind, how can I do it?

Thanks a lot

Hi,

You can't bind a SQL Operation to a Mapping directly but you could use the Execute Delivery Action to do that.

1. Publish the Mapping delivery to the Runtime (Right click > Publish > Delivery)
2. Bind your SQL Operation to an Execute Delivery action that executes the Mapping's delivery
3. Use the bind variable to send your value to the delivery (so to your Mapping)

E.g.



Edit:
This is similar to what is explained in this article:
stambia.org/doc/80-process/how-to/192-ho...les-to-a-sub-process

Hi,

Personnaly, I don't really like the use of direct bind. You have to build delivery for each mapping with this way, and it becomes quickly hard to maintain.

There is another way that I prefer (in PJ, too) :



Just replace the values of $$$QUERY$$$ and the variable with what you need.
I use "RANK() OVER(ORDER BY ...)" to get an unique ID by row. Of course, you have to adapt it to your SGBD.

Have a nice week-end.

Emmanuel

Thanks guys!

I tried the solution proposed by Emmanuel and it worked perfectly!
Actually, I prefer to use the RANK method because it's easier to maintain.

Anyway, thanks again!

Have a good week-end