Conditional Statement In A For Each

I have a FOR EACH with which I need some help. It is a picklist table. I want to display records, but I have some records that are incomplete or open. If the user selects to see only these lines I want to restrict my FOR EACH on that information. The table has a small number of records today, but it will certainly grow. The code below is what I started with for getting the records I want. I want to pull the condition if open-only into my FOR EACH. I tried an example that I found in the progress documentation, but I could not get it to work in my instance.
Code:
    for each xxpick_mstr no-lock
        where xxpick_domain = "usa"
        and xxpick_site >= site and xxpick_site <= site1
        and xxpick_nbr >= mnbr and xxpick_nbr <= mnbr1
        and xxpick_prline >= pline and xxpick_prline <= pline1
        and xxpick_part >= part and xxpick_part <= part1
        by xxpick_site by xxpick_nbr by xxpick_part
        by xxpick_print_date by xxpick_print_time
        with frame b width 132 no-box down:
        if open-only and xxpick_status <> "" then next.

Progress example:
Code:
f WHERE a >= (IF a1 NE 0 THEN a1
              ELSE -99999999) AND
        a <= (IF a1 NE 0 THEN a2
              ELSE +99999999)
I added the following at the end (before first sort):
and xxpick_status = (if open-only = yes then rpt-stat)

i set rpt-stat = "" at the top of the procedure. I did not add an else, because I don't need one. If open-only is no then include all records.

Any ideas?
David
 
Last edited by a moderator:
I have a FOR EACH with which I need some help. It is a picklist table. I want to display records, but I have some records that are incomplete or open. If the user selects to see only these lines I want to restrict my FOR EACH on that information. The table has a small number of records today, but it will certainly grow. The code below is what I started with for getting the records I want. I want to pull the condition if open-only into my FOR EACH. I tried an example that I found in the progress documentation, but I could not get it to work in my instance.

for each xxpick_mstr no-lock
where xxpick_domain = "usa"
and xxpick_site >= site and xxpick_site <= site1
and xxpick_nbr >= mnbr and xxpick_nbr <= mnbr1
and xxpick_prline >= pline and xxpick_prline <= pline1
and xxpick_part >= part and xxpick_part <= part1
by xxpick_site by xxpick_nbr by xxpick_part
by xxpick_print_date by xxpick_print_time
with frame b width 132 no-box down:
if open-only and xxpick_status <> "" then next.


Progress example:
f WHERE a >= (IF a1 NE 0 THEN a1
ELSE -99999999) AND
a <= (IF a1 NE 0 THEN a2
ELSE +99999999)
I added the following at the end (before first sort):
and xxpick_status = (if open-only = yes then rpt-stat)

i set rpt-stat = "" at the top of the procedure. I did not add an else, because I don't need one. If open-only is no then include all records.

Any ideas?
David

This is wrong; "and xxpick_status = (if open-only = yes then rpt-stat)"; an "else" is mandatory on the sentence.

I would change it to:
and (open-only = FALSE OR xxpick_status = rpt-stat)

Other option, similar to your first code is:
and xxpick_status = (if open-only = yes then rpt-stat else xxpick_status)

Probably both run at same speed, Im not sure.
 
Last edited:

Cringer

ProgressTalk.com Moderator
Staff member
Don't do it. You will almost certainly be forcing a full table scan whatever you do. Take the hit now and build a dynamic query. Your users will thank you in the future.
 
Thanks to all of you for the help. I did a query dependent upon open or closed and then looped through the data that was produced and it worked perfectly. The procedure now runs in mere seconds.
 
Top