FOR EACH query resolution

[Rob Fitzpatrick]

A developer came to me with an ABL question (10.2B07). If a WHERE clause of a FOR EACH contains an equality match on a variable, and the variable's value gets changed within the FOR EACH, does the WHERE clause get re-evaluated to incorporate the new value? Let me explain with an example.

define variable v-i as integer initial 1 no-undo.

for each table 
where table.seq eq v-i
and table.custnum eq 100 no-lock:
  display table.lname 
          table.seq.
  v-i = v-i + 1.
  display v-i.
end.

In this case, seq is a component of the primary index, and that index is being used (as per compile xref and -zqil). Based on the results returned, it appears that the first part of the WHERE clause, table.seq = v-i, becomes table.seq = 1 and remains so through all iterations of the loop. That would make sense to me, as the server would need to bracket on the index, and have that bracket remain consistent as it iterates through the matching records.

I'm not looking for thoughts on the business logic or quality of the code (I have my own! ). Just want to confirm that you can't change the equality match in this way from one iteration to the next. In other words, that the equality match remains static ( = 1, in this case) for all iterations of the FOR loop. I hope that makes sense.

I read the FOR statement info in the docs but didn't find a direct answer to this question. Thanks in advance for any help.

 

[Marian EDU]

it's only evaluated once at the beginning, changing it in the loop it does not change the filter condition... expected behavior in my view

 

[Rob Fitzpatrick]

Thanks Marian.

 

[RealHeavyDude]

Additionally, although you loop through the records iteratively in the ABL execution layer, the database engine does some look ahead. When it is a remote client it will even send as much records over the network as will fit into one message buffer ( which size is define by the -Mm parameter whose value must be set equally between the database and the remote clients and has, IIRC, 1024 per default - which is a very conservative value, BTW ). All of this, or let's rephrase it, efficient data retrieval in any form, would not be possible if the WHERE clause of the FOR EACH would be evaluated on each iteration.

Heavy Regards, RealHeavyDude.

 

[D.Cook]

Ah ha, so that explains why user defined functions are not evaluated on each iteration (even when it refers to a database field)

ie:

for each table
   where myFunction(table.seq) eq v-i
   no-lock:
   display table.seq.
 end.

(myFunction is only evaluated once at the start of the query)

 

[RealHeavyDude]

Yes. Because otherwise the performance hit would be huge - most likely the same as you would be table scanning on every for each you code.

Heavy Regards, RealHeavyDude.