| Author |
Message
|
| semcgurk |
 Posted: Thu Nov 14, 2013 1:30 am Post subject: Recursion Problem |
 |
|
Newbie
Joined: 14 Nov 2013
Posts: 9
|
Hi there,
I'm having an issue with my code - I'm trying to set up a loop whereby I loop round an array of items and try and extract a number of arrays from this:
| Code: |
DECLARE N INTEGER CARDINALITY
(Environment.Names[]);
DECLARE I INTEGER 1;
WHILE I <= N DO
SET OutputRoot.SOAP.Body.Person[] =
(SELECT ITEM
(SELECT Person.FirstName AS FirstName,
Person.SecondName AS SecondName
FROM People.Person[] AS Person
WHERE Person.SecondName = Environment.Names[I])
FROM InputRoot.XMLNSC.People[] AS People
);
SET I = I + 1;
END WHILE;
|
However, when I run this, it only ever returns the results of the last instance of OutputRoot.SOAP.Body.Person[] where this matches Environment.Names[] - I would expect it to return all the instances in which these match.
Can anyone give me any help on where I am going wrong?
Thanks |
|
| Back to top |
|
 |
| Simbu |
| Posted: Thu Nov 14, 2013 2:23 am Post subject: |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
| What is your expected output structure? User Trace will provide you the reason for the behavior. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Nov 14, 2013 2:48 am Post subject: Re: Recursion Problem |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| semcgurk wrote: |
However, when I run this, it only ever returns the results of the last instance |
Of course it will. You are overriding OutputRoot.SOAP.Body.Person[] with every iteration. |
|
| Back to top |
|
|
| semcgurk |
| Posted: Thu Nov 14, 2013 3:17 am Post subject: |
|
|
Newbie
Joined: 14 Nov 2013
Posts: 9
|
@chevalier - yes, I think that's right - thanks for this!
How can I prevent this from happening?
@sibmu - the expected output is
| Code: |
<Body>
<Person>
<FirstName>Bruce</FirstName>
<SecondName>Lee</FirstName>
</Person>
<Person>
<FirstName>Jackie</FirstName>
<SecondName>Chan</FirstName>
</Person>
<Person>
<FirstName>Omar</FirstName>
<SecondName>Sharif</FirstName>
</Person>
</Body>
|
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Nov 14, 2013 3:19 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You change the behavior of the code by changing the code...
Also, you are using [] to address elements in the logical message tree. This will run slowly. You should be using reference variables instead.
If you want to assign a new Person for each loop through, then you need to create a new Person each time you loop through and then assign it. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Nov 14, 2013 3:22 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
do not loop.
Select where secondname IN ( name[1], name[2], etc )
You will need to work out how to do the ( name[1] , ..... name[n] ), probably a loop and store it somewhere, and probably use the EVAL function. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Nov 14, 2013 3:32 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
and by the way, the expected message you posted is invalid XML
| Code: |
<Body>
<Person>
<FirstName>Bruce</FirstName>
<SecondName>Lee</FirstName>
</Person>
<Person>
<FirstName>Jackie</FirstName>
<SecondName>Chan</FirstName>
</Person>
<Person>
<FirstName>Omar</FirstName>
<SecondName>Sharif</FirstName>
</Person>
</Body>
|
After Body, you should have only one Root element.
Something like
| Code: |
<Body>
<OutputMessage>
<Person>
<FirstName>Bruce</FirstName>
<SecondName>Lee</FirstName>
</Person>
<Person>
<FirstName>Jackie</FirstName>
<SecondName>Chan</FirstName>
</Person>
<Person>
<FirstName>Omar</FirstName>
<SecondName>Sharif</FirstName>
</Person>
</OutputMessage>
</Body>
|
Last edited by dogorsy on Thu Nov 14, 2013 9:05 pm; edited 1 time in total |
|
| Back to top |
|
|
| Esa |
| Posted: Thu Nov 14, 2013 3:40 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| dogorsy wrote: |
do not loop.
Select where secondname IN ( name[1], name[2], etc )
You will need to work out how to do the ( name[1] , ..... name[n] ), probably a loop and store it somewhere, and probably use the EVAL function. |
Yes. Check what the infocenter of your broker version says about IN operator.
In version 8 ou should be able to do something like this:
| Code: |
| ...WHERE lastname IN (SELECT Names FROM Environment AS Names) |
|
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Nov 14, 2013 4:03 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Esa wrote: |
In version 8 ou should be able to do something like this:
| Code: |
| ...WHERE lastname IN (SELECT Names FROM Environment AS Names) |
|
yes, that is a good one to try ! |
|
| Back to top |
|
|
| semcgurk |
| Posted: Thu Nov 14, 2013 5:03 am Post subject: |
|
|
Newbie
Joined: 14 Nov 2013
Posts: 9
|
Guys,
Thanks so much for the replies - these have really helped me out.
This:
| Code: |
...WHERE lastname IN (SELECT Names FROM Environment AS Names)
|
looks like the perfect option except I'm working in message broker version 6.1 
So I'll have to work out how to do the ( name[1] , ..... name[n] ) - by the sounds of things, the EVAL function is most suited to this? |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Nov 14, 2013 5:10 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
You need to loop and concatenate all the values in a variable.
| Code: |
declare A char '';
set A = A||','||name[I];
Then use A in the select statement with eval. You will need to try different things to get it right. You might also need to construct the whole of the select statement in a variable and then use eval. |
|
|
| Back to top |
|
|
| joebuckeye |
| Posted: Thu Nov 14, 2013 5:34 am Post subject: Re: Recursion Problem |
|
|
Partisan
Joined: 24 Aug 2007
Posts: 365
Location: Columbus, OH
|
| semcgurk wrote: |
| Code: |
DECLARE N INTEGER CARDINALITY
(Environment.Names[]);
DECLARE I INTEGER 1;
WHILE I <= N DO
<snip>
SET I = I + 1;
END WHILE;
|
|
Please don't use arrays to loop through the code. Poor performance. Use a FOR loop with references instead.
| Code: |
FOR nameRef AS Environment.Names[] DO
<do work here>
END FOR;
|
Let the FOR statement handle all the looping logic. |
|
| Back to top |
|
|
| semcgurk |
| Posted: Thu Nov 14, 2013 6:14 am Post subject: |
|
|
Newbie
Joined: 14 Nov 2013
Posts: 9
|
| Thanks all for your replies - I think I have enough information now to give this a crack - thanks again! |
|
| Back to top |
|
|
|
|
