Welcome Guest! Log in

Topic-icon Question Using loop in a process

More
09 Sep 2016 11:55 #1 by letancel
letancel created the topic: 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

Please Log in or Create an account to join the conversation.

More
09 Sep 2016 15:44 - 09 Sep 2016 15:47 #2 by Cyril Dussud
Cyril Dussud replied the topic: Using loop in a process
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
Attachments:
Last Edit: 09 Sep 2016 15:47 by Cyril Dussud.

Please Log in or Create an account to join the conversation.

More
09 Sep 2016 16:56 - 09 Sep 2016 17:00 #3 by Emmanuel Rambeau
Emmanuel Rambeau replied the topic: Using loop in a 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
Attachments:
Last Edit: 09 Sep 2016 17:00 by Emmanuel Rambeau.

Please Log in or Create an account to join the conversation.

More
09 Sep 2016 17:38 #4 by letancel
letancel replied the topic: Using loop in a process
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

Please Log in or Create an account to join the conversation.