Answered Can I use a For Each for dynamic input in some circumstances

P

progdev1

Member
Hi All,
I have a code that goes
Code: 
ASSIGN dtLastUpdate = TODAY - 30.
FOR EACH ORDER WHERE depot-num = currentDepot
                                    AND last-update >= dtLastUpdate NO-LOCK:
EXPORT ORDER.
END.

I have a new request to give the user the option to choose all date as well as the last 30 days. I know I can use a dynamic query to do this. However I'd like to avoid re-writing the program if this is possible. Is there any way I can alter the FOR EACH to do this.

I was thinking something like
Code: 
ASSIGN dtLastUpdate = TODAY - 30.


    FOR EACH ORDER WHERE depot-num = currentDepot
                                    AND (IF chOptionChosen = "All" THEN TRUE
                                              ELSE last-update >= dtLastUpdate)
                                NO-LOCK:

    EXPORT ORDER.
END.
I'm not sure if the above will work correctly or if it will cause big performance impact, on the search give the fields depot-num and last-update are an index. I'd appreciate any guidance on this.
 
There is a better way. Several actually.

You could go the full handle based build a dynamic query from scratch route but that's a big step if you are not already comfortable with handles.

An intermediate step that blends well with traditional static code approaches is something similar to this:

Code: 
define variable option as character no-undo.

define query q for order.

update option.    /* I will leave fancy UI as an exercise for the reader... */

if option = "all" then
 open query q for each order no-lock.
 else
  open query q for each order no-lock where last-update > ( today - 30 ). 

get first q.
do while available order:

  export order.

  get next q.

end.

close query q.
 
Yes, you need a QUERY not a FOR EACH.

You cannot write an efficient WHERE clause that will support arbitrary user specification of selection criteria in a single FOR EACH.
 
Hey Tom, out of interest more than anything, is there a particular reason why you do
Code: 
get first q.
do while available order:

  export order.

  get next q.

end.
rather than something like
Code: 
do while query q:get-next:

  export order.

end.

It's a trivial difference here, but I would generally favour the latter in a complex loop as it means you always have the next record on the next iteration. Just wondering really if you intentionally coded it your way for a reason, or just because that's how you coded it?
 
You must log in or register to reply here.
Back
Top