Subject:FLOWR Loop Counter Author:Keith B Date:02 Aug 2007 11:28 AM
I'm trying to get a simple loop counter working for some xml I need to generate out of a table. Without the loop counter everything works perfectly and the mapping takes only a few seconds. Example (simplified):
{
for $DETAIL in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and ($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
return
<GROUP_3>
<LX>
<ANCHOR id="LoopCounter">
<LX01>1</LX01>
</ANCHOR>
</LX>
</GROUP_3>
}
When I add the 'at' statement to the 'for' the preview "goes out to lunch" for 10 minutes or longer before returning. When it finally does return, it seems to work but the counter starts at 2 rather than at 1 like I would expect.
{
for $DETAIL at $i in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and ($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
return
<GROUP_3>
<LX>
<ANCHOR id="LoopCounter">
<LX01>{$i}</LX01>
</ANCHOR>
</LX>
</GROUP_3>
}
Am I doing something wrong or is there another approach I could take? Thank you for your time.
Subject:FLOWR Loop Counter Author:Ivan Pedruzzi Date:02 Aug 2007 12:39 PM
Hi Keith,
Could you tell us which XQuery processor is selected in the scenario dialog and which Stylus Studio build are you running (Help -> About -> Framework Version)?
If you are running using DataDirect XQuery with Stylus Studio 2007 Rel. 2 could you send the query plan to stylus-field-report@progress.com?
Subject:FLOWR Loop Counter Author:Marc Van Cappellen Date:02 Aug 2007 04:36 PM
Keith,
The first query,
for $DETAIL in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and
($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
is translated straight into SQL. I.e. the where clause will end up in the SQL statement and only minimal data will be retrieved out of the database.
With the second query,
for $DETAIL at $i in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and
($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
The "at $i" is not translated into SQL. We say that it is "compensated" by DataDirect XQuery. As such the where clause can't be pushed to the database and as a result the complete DETAIL table is retrieved to the client.
Second, you indicated that the counter starts at two. That might well be the case yes. Note that the $DETAIL/$i tupples are first produced and only afterwards the where clause is evaluated. As such it is likely that certain tupples will be filtered out, and for example in your scenario this happens for the first tupple (likely also many others are filtered).
So we have two issues here... How can we resolve them?
I would try the following query (not tested, hopefully it doesn't include syntax errors).
a) the inner flwor can be translated into SQL which will resolve your performance issue
b) the top-level flwor doesn't have a where clause and as such the results will include incremental numbers 1,2,3,etc
for $d at $i in (for $DETAIL in collection("DBNAME.dbo.DETAIL")/DETAIL
where ($DETAIL/CUSTOMERKEY = $ck) and
($DETAIL/HEADERKEY = $DETAIL/HEADERKEY)
return $DETAIL)
return
<GROUP_3>
<LX>
<ANCHOR id="LoopCounter">
<LX01>{$i}</LX01>
</ANCHOR>
</LX>
</GROUP_3>