on 08-21-2014 2:36 PM
Hi experts,
I would like to do a SQL querry in an atom SQL call inside a for each atom like that :
My for each atom get back each result of my first sqlCall and my query in my second sqlCall compare an element with the current element of the for each atom like that : Select element from table where element1=current element of for each atom (which is one of results of first sql call)
I don't know how to get back the current element in my query
Does anyone know?
Thank you
Regards
Sarah
Hi Sarah, can you put here a picture of the structure of the scenario, is not easy to see that picture.
Do you want to read a value resultant of a Payload Role="C" sql call?
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi silvio,
This is my scenario :
The trigger is the creation of order
My first SqlCall get back all item of the order.
The for each loop on the result of this query and in the sql call inside the for each I would like do this query :Select element from table where element1=current element of for each atom (which is one of results of first sql call) but I don't know how to get back the current element of the loop.
So yes I would like to read a value resultant of a Payload Role="C" sql call (the first) but the current
Can you help me please?
Thanks
Sarah
Hi Sara, now I get it, that will be easy, what expression do you have defined in the for each?
But first you need to add an atom after the first sql call, in that atomX create a xsl:foreach that will parse the result of the query in something like this:
<Items>
<Item>item01</Item>
<Item>item02</Item>
<Item>item03</Item>
<Item>item04</Item>
<Item>item05</Item>
<Item>item06</Item>
</Items>
Then in the expression in the foreach atom use the Xpat like this:
/vpf:Msg/vpf:Body/vpf:Payload[./@Role='X' and ./@id='atom1']/Items/Item
So after this, the result of the foreach atom will be: <Item>item01</Item>, and this is exactly what you need.
Then you only need the proper xpath expression to read the item code "item01".
Hope this help.
Regards.
Hi Silvio,
First in the for each I have defined this expression: /jdbc:Row/jdbc:ItemCode to get back results of the sqlCall before it's correct? Or do I have absolutely need to add an atom after the first SqlCall?
Then, I don't really understand how I can define the current element. I can use the path to read the item code "item01" but I don't understand how to read the other
Sorry I am very novice in for each atom
Thank you for your help
Sarah
Hi,
I have this
So I suppose that it doesn't work?
I think I don't understand the difference between the current element and the current node of the for each.
The atom inside the for each will do an action (a query for example) for each element of the result of the expression of the for each right?
For each item result of the first sql call I need to do another query
Hum... something is not OK, how many lines you have in the result of the query 1 ?
Lets said you have 10 lines items... the foreach should have 10 results...
This will not work... expression: /jdbc:Row/jdbc:ItemCode
Will be easier if you add an extra atom and organize the payload for the foreach call like i suggested.
Regards
Hi silvio,
I add an xform atom after my first sql call.
It's ok in my xform I have that:
<Items>
<item>item001</item>
</Items>
I try just with one item to start.
In for each expression I put exactly like you suggest with the correct id of my xform atom.
But now when I debbug I have this
After the xform atom I have the correct result but nothing go out from the for each..
Any suggestions?
Thanks
sarah
Please change to this:
<Items>
<Item>
<ItemCode>item01</ItemCode>
</Item>
<Item>
<ItemCode>item02</ItemCode>
</Item>
<Item>
<ItemCode>item03</ItemCode>
</Item>
<Item>
<ItemCode>item04</ItemCode>
</Item>
</Items>
The output should be:
<ItemCode>item01</ItemCode>
And send me the ouput of atom xform and the output of the foreach.
Regards.
Hi
Did you replace atom1 by atom4 here?
Then in the expression in the foreach atom use the Xpat like this:
/vpf:Msg/vpf:Body/vpf:Payload[./@Role='X' and ./@id='atom1']/Items/Item
If true then try this:
/vpf:Msg/vpf:Body/vpf:Payload[./@Role='X' and ./@id='atom4']/*[local-name()='Items']/Item
Regards
Hi Radek,
I try to modify my xpath to a shorter version but it's similar. Nothing go out from the for each. I don't understand.. Do you see others things which could cause this issue?
Yes thank you I was able to download and import your scenario I didn't get the time to work on mine but I will try
Thanks
Regards
Sarah
Hi leo,
Thank you
I have one more question.
Now the output of my for each is that :
So it's ok.
After that I have a Sql Call and I would like get back to my query the current element of the for each. e.g SELECT U_ArtRg1 from [@SAV0001] where U_ArtRef=Current element of the for each
I don't know what is the syntax for that can you help me please?
Thanks
Regards
sarah
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.