Question about loop component in xaction

[nashrul]

Hi, in xaction, when we use loop component to iterate on the resultset for sqlquery component, do we need to put a logic to end the loop or it will stop when there is no more record to loop on ??
I have a strange behavior of xaction (file attached). I write an xaction that queries a database for parameters and email name. In this solution file, it will iterate over the fetched records and doing the query again to get records for the report and send it as attachment to the appropriate recipient.

Here's the structure of my xaction:


SQLLookupRule
UtilityComponent(FormattedMessage)
Loop
JavascriptRule
SQLLookupRule
JFreeReportComponent
EmailComponent
Strangely, I only have 2 matched records for the first SQLLookupRule component above, but when I see pentahoAudit log, it keeps iterating over and over.
Any ideas on how to properly loop on fetched records ??
Any help would be greatly appreciated.
Thanks..
PS: When we reference variable from another component what's the use of "PREPARE" word ?? What's the different between using and not using this word when we reference a variable (like the one {email} in my solution example) ??

[nashrul]

I looked at sample solution BurstActionSequence.xaction..I changed my xaction file..I just need to put if condition component


rs.getRowCount()>0
before the first iteration is hit.
It works now..

[vidhya]

I have a javascript where i have written the following:
var rows = java.lang.reflect.Array.newInstance(java.lang.String,9);
var i;
for (i=0;i<query_result.getRowCount();i++){
var c=query_result.getValueAt(i,0);
rows[i]=c;
return rows[i];}

The output of this script is "results" of type:string-list
Now,i have a following relational component:
SELECT x FROM tablename where col1<={PREPARE:results}
I need x for each and every value in "results".Right now,am only getting for first value of results which i guess is cos of the way this query is written.
Someone pls tell me How should i modify the query?

thanks...

[pstoellberger]

I don't know what your query_result looks like, but why dont you put that all together in one query?
you want all x where col1 <= results, which means the biggest value in results should be enough to get all the x you want, right?
SELECT x FROM tablename where col1<= (select max(your_column) from xyz) < the sub query is the query you perform for query_result

-paul

[jblackcat]

I have a litle updating on pstoellberger's post:

I think if

query_result = (SELECT your_column FROM xyz)

so the following one will solve your problem, it merges two queries in one:


SELECT x FROM tablename where col1<= (SELECT min(your_column) FROM xyz)

[vidhya]

hi paul,
I found a way to execute my xaction without the script. I was using the script for some zero value error in x column..I either had to change script or relational query...
Im now able to pass column x from my mdx directly to the relational component's query....
thanks anyway....