1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Conditional Statement In A For Each

Discussion in 'Development' started by dlangschied, Jul 25, 2017.

  1. dlangschied

    dlangschied Member

    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 (progress):
    1.  
    2.     for each xxpick_mstr no-lock
    3.         where xxpick_domain = "usa"
    4.         and xxpick_site >= site and xxpick_site <= site1
    5.         and xxpick_nbr >= mnbr and xxpick_nbr <= mnbr1
    6.         and xxpick_prline >= pline and xxpick_prline <= pline1
    7.         and xxpick_part >= part and xxpick_part <= part1
    8.         by xxpick_site by xxpick_nbr by xxpick_part
    9.         by xxpick_print_date by xxpick_print_time
    10.         with frame b width 132 no-box down:
    11.         if open-only and xxpick_status <> "" then next.
    12.  
    Progress example:
    Code (progress):
    1.  
    2. f WHERE a >= (IF a1 NE 0 THEN a1
    3.               ELSE -99999999) AND
    4.         a <= (IF a1 NE 0 THEN a2
    5.               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: Jul 26, 2017
  2.  
  3. Marco Mendoza

    Marco Mendoza Member

    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: Jul 26, 2017
  4. Cringer

    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.
     
    RealHeavyDude likes this.
  5. dlangschied

    dlangschied Member

    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.
     
  6. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    With how much data? ;)
     

Share This Page