Question Condition Basis - For each

[Reynold]

Hello All -

I am stuck in one situation where I need to understand - How can I achieve the For each block on the basis of some condition.

I am using OpenEdge 11.1.

I want to achieve something like that as I mentioned below (I know the below one is erroneous):
If condition-1 then do:
for each customer where custno >= 123 and date >= 12/12/12 and date <= 06/06/13 no-lock:
end.
else if condition-2 then do:
for each customer where custno >= 456 and date >= 04/12/13 and cust-name begins "Hav" no-lock:
end.
else do:
for each customer where custno >= 789 and cust-phone contains "065" no-lock:
end.

display cust-name
cust-phone
cust-no.
end.

 

[jdpjamesp]

You have a few options.
1)

If condition-1 then
  OPEN QUERY CusQuery for each customer where custno >= 123 and date >= 12/12/12 and date <= 06/06/13 no-lock.
else if condition-2 then
  OPEN QUERY CusQuery for each customer where custno >= 456 and date >= 04/12/13 and cust-name begins "Hav" no-lock.
else
  OPEN QUERY CusQuery for each customer where custno >= 789 and cust-phone contains "065" no-lock.
 
GET FIRST CusQuery.
DO WHILE NOT QUERY CusQuery:QUERY-OFF-END:
  DISPLAY cust-name
    cust-phone
    cust-no.
  GET NEXT CusQuery.
END.

(Note this hasn't been tested for compile or logic errors)

2) Look into dynamic queries.

 

[Stefan]

And if you want to go down an awfully windy road you can always convult it all into a single static for each:

DEFINE TEMP-TABLE customer
  FIELD cust-no AS INT
  FIELD DATE AS DATE
  FIELD cust-name AS CHAR
  FIELD cust-phone AS CHAR
INDEX ttcustix IS UNIQUE PRIMARY cust-no
INDEX ttwordix IS WORD-INDEX cust-phone .
 
 
DEF VAR condition-1 AS LOGICAL.
DEF VAR condition-2 AS LOGICAL.
 
FOR EACH customer
  WHERE (
            condition-1 AND
            (
                    cust-no  >= 123
              AND  date    >= 12/12/12
              AND  date    <= 06/06/13
            )
        )
  OR    (
            condition-1 = FALSE AND condition-2 AND
            (
                    cust-no    >= 456
              AND  date        >= 04/12/13
              AND  cust-name BEGINS "Hav"
            )
        )
  OR    (
            condition-1 = FALSE AND condition-2 = FALSE AND
            (
                    cust-no    >= 789
              AND  cust-phone  CONTAINS "065"
            )
 
        )
NO-LOCK:
 
  DISPLAY
      cust-name
      cust-phone
      cust-no.
 
END.

Note: the last time I have seen anything as convulted as above was in code that had to work with Progress 8.3 (my query alignment skills have improved since then )

 

[jdpjamesp]

Note 2: The above code from Stefan, whilst technically correct, is likely to be hugely inefficient. Personally I would go for the dynamic query route.

define variable lv-Handle as handle no-undo.
define variable lv-QueryString as character no-undo.
 
/*Some code to build your query string*/
 
create query lv-Handle.
lv-Handle:set-buffers (buffer Customer:handle).
lv-Handle:query-prepare(lv-QueryString).
lv-Handle:query-open().
lv-Handle:get-first().
 
do while not lv-Handle:query-off-end:
  /*Whatever you want to do*/
  lv-Handle:get-next().
end.
 
if valid-handle(lv-Handle) then
  delete object lv-Handle.

 

[Stefan]

Note 2: The above code from Stefan, whilst technically correct, is likely to be hugely inefficient.

Before calling me a fraud show me an xref in which index selection is shown as broken! ;-)

Conditions 1 and 2 will use the same index - so no problem there - the third one may however have forced the index to switch to the word index for all three.

Personally I would go for the dynamic query route.

Definitely. But - since you are using dates - beware how these are handled. Either dates are screwy or decimals are screwy - or both are quirky - depending on or not on your session settings.