Can I specify the contents of the buffer while creating it in Progress-4GL?

Question

Noob to Progress here and self-taught while working so sorry if I missed some obvious things. I learned about buffers yesterday and I'd like to know if it's possible to delimit the scope of what the buffer will search. Below, an example of what I want to know.

DEFINE BUFFER ex1 FOR emit WHERE emit.id > 50000.

FOR EACH ex1:
    DISP ex1.id ex1.name.
end.

I know I could put the WHERE on the FOR EACH part in this example, but I'd like to know if and how can I delimit the buffer so I can do what I intend to in my code here.

Thanks for the help.

EDIT: I'll put the code I wrote here so it's easier to understand what I meant. I really don't see any other solutions besides using temp-tables and it makes everything so much slower. I'll keep my code like this but if anyone knows of a better solution, just give me a shout. Thanks again for your time.

def var contador as int.
def var contador2 as int.
def var contador3 as int.

def temp-table tt-min-oper 
field it-codigo like operacao.it-codigo
field num-id-operacao like operacao.num-id-operacao
field op-codigo like operacao.op-codigo
.

def temp-table tt-oper 
field it-codigo like operacao.it-codigo
field op-codigo like operacao.op-codigo
.

def temp-table tt-valida-oper 
field it-codigo like operacao.it-codigo
.

for each operacao NO-LOCK
break by operacao.it-codigo by operacao.num-id-operacao:
    IF FIRST-OF (operacao.it-codigo) THEN DO:
        
        CREATE tt-min-oper.
        ASSIGN
        tt-min-oper.it-codigo = operacao.it-codigo
        tt-min-oper.num-id-operacao = operacao.num-id-operacao
        tt-min-oper.op-codigo = operacao.op-codigo
        .
    END.
END.

FOR EACH tt-min-oper WHERE tt-min-oper.op-codigo <> 10 NO-LOCK:
    create tt-oper.
    assign tt-oper.it-codigo = tt-min-oper.it-codigo.
END.


FOR EACH operacao NO-LOCK,
    EACH tt-oper
    WHERE operacao.it-codigo = tt-oper.it-codigo
    AND operacao.op-codigo = 10 NO-LOCK:
       
        create tt-valida-oper.
        assign
        tt-valida-oper.it-codigo = operacao.it-codigo.
    
END.

Answer 1

The code I posted before took several minutes to compile. I managed to reduce it a lot by putting a FOR EACH inside another (now it only takes 25 seconds). Unfortunately, I can't filter op-codigo <> 10 in the first FOR EACH because it changes the results. I didn't use USE-INDEX because I read Tom Bascom saying it wasn't good, but we have indexes in the tables we're using. I don't know a lot about the tables, because I'm fairly new here and I'm still learning a lot.

So my code became like the example below. I don't know if it's good to put a FOR EACH inside another and I always avoided it, but all my colleagues do it.

DEF TEMP-TABLE tt-min-oper 
FIELD it-codigo         LIKE operacao.it-codigo
FIELD num-id-operacao   LIKE operacao.num-id-operacao
FIELD op-codigo         LIKE operacao.op-codigo
.

DEF TEMP-TABLE tt-valida-oper 
FIELD it-codigo LIKE operacao.it-codigo
FIELD num-id-operacao   LIKE operacao.num-id-operacao
.


FOR EACH operacao NO-LOCK
BREAK BY operacao.it-codigo BY operacao.num-id-operacao:
    IF FIRST-OF (operacao.it-codigo) THEN DO:
    CREATE tt-min-oper.
       ASSIGN
       tt-min-oper.it-codigo = operacao.it-codigo
       tt-min-oper.num-id-operacao = operacao.num-id-operacao
       tt-min-oper.op-codigo = operacao.op-codigo
       .          
     END.
 END.

FOR EACH tt-min-oper
WHERE tt-min-oper.op-codigo <> 10 NO-LOCK:
    FOR EACH operacao
    WHERE operacao.it-codigo = tt-min-oper.it-codigo
    AND operacao.op-codigo = 10 NO-LOCK:
        CREATE tt-valida-oper.
        ASSIGN
        tt-valida-oper.it-codigo = operacao.it-codigo
        tt-valida-oper.num-id-operacao = tt-min-oper.num-id-operacao
        .
    END.
END.

I couldn't find a solution for buffers like I wanted, but I managed to do it in a way I've never done before, so I see this as a victory. Thanks for your time and suggestions Mike and if there's any other suggestion, I'm all open to it.

Answer 2

Answer to the extended question of the OP.

Answering this without knowing your use-case and details about the table relations is always difficult. But from your code:

In the final FOR EACH, you're only iterating operacao records where first the it-codigo is unique (then you put it into tt-min-oper). Then you filter tt-min-oper for op-codigo <> 10 and add the resulting records into tt-oper.

So at this time tt-oper should contain records with unique it-codigo values and op-codigo <> 10.

So at the minimum you don't need this loop here:

FOR EACH tt-min-oper WHERE tt-min-oper.op-codigo <> 10 NO-LOCK:
    create tt-oper.
    assign tt-oper.it-codigo = tt-min-oper.it-codigo.
END.

as in the initial FOR EACH you could filter on op-codigo <> 10 as well:

for each operacao WHERE operacao.op-codigo <> 10 NO-LOCK
break by operacao.it-codigo by operacao.num-id-operacao:

How many records are in the able operacao? And is there an index with it-codigo as the first field? The FOR EACH with BREAK-BY will still retrieve all records in the table, but only process the FIRST-OF (it-codigo) ones. That may be a very heavy operation.

In large tables, if may be better to do something like this instead of a FOR EACH with BREAK-BY. My Order table has 700000 records, so this here processes all 700000 records:

FOR EACH Order BREAK BY Order.Salesrep:
        
    IF FIRST-OF (Order.Salesrep) THEN
    DO:
        DISPLAY Order.Salesrep . 
    END.
END.

And this here get'S the same result, but only reads 10 records (there are 10 Salesreps in the DB). But this is only possible as there is an index for the Salesrep field.

DEFINE VARIABLE cPrevious-Salesrep AS CHARACTER NO-UNDO . 

FIND FIRST Order WHERE Order.Salesrep > cPrevious-Salesrep
    NO-LOCK NO-ERROR . 
    
DO WHILE AVAILABLE (Order):
    DISPLAY Order.Salesrep WITH DOWN . 
    DOWN 1 .
    
    ASSIGN cPrevious-Salesrep = Order.Salesrep. 

    FIND NEXT Order WHERE Order.Salesrep > cPrevious-Salesrep
        NO-LOCK NO-ERROR . 
END.

So to optimize your code, you need to know your DB schema and actual data.

Answer 3

You limit results in the query, the FOR EACH statement in your case, so

DEFINE BUFFER ex1 FOR emit .

FOR EACH ex1 WHERE ex1.id > 50000:
    DISP ex1.id ex1.name.
end.