CODEWITHSUNDEEP
openedgeprogress-4gl
Thiru
Thiru

Reputation: 251

How to fix Dynamic Query error for progress 4gl?

I am new to progress 4GL. I have written logic for buffer by adding multiple tables into a single query and finding the records from another table by using table field names. I am not sure why I am getting errors. Please help to change the logic

DEFINE VARIABLE ix AS INTEGER NO-UNDO.
DEFINE VARIABLE qh AS HANDLE  NO-UNDO.
DEFINE VARIABLE bh AS HANDLE  NO-UNDO.
DEFINE VARIABLE fh AS HANDLE  NO-UNDO EXTENT 10.
DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO.

CREATE BUFFER bh FOR TABLE "Customer, Invoice".
CREATE QUERY qh.

ASSIGN
    cQuery = "FOR EACH Customer NO-LOCK, EACH Invoice WHERE Invoice.Cust-Num = Customer.Cust- 
 Num NO-LOCK: ".

qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE(cQuery).
qh:QUERY-OPEN().
qh:GET-FIRST().

 /* Field Invoice.Cust-Num is already defined in cQuery*/

  FIND Order WHERE Order.Cust-Num =  Invoice.Cust-Num NO-LOCK  NO-ERROR.
IF NOT AVAILABLE Order THEN DO:
  FIND Ref-Call WHERE Ref-Call.Cust-Num = Invoice.Cust-Num NO-LOCK NO-ERROR.
DISPLAY Ref-Call.Cust-Num.
END.


qh:QUERY-CLOSE().
bh:BUFFER-RELEASE().
DELETE OBJECT bh.
DELETE OBJECT qh.

enter image description here

Upvotes: 0

Views: 1782

Answers (2)

Mike Fechner
Mike Fechner

Reputation: 7192

Looking at the field names, you're using the classic Sports Database for your training. There's a "newer" Sports2000 demo database with a little bit more of data that might be worth playing with.

There are multiple issues in that program.

First, you cannot define a single dynamic buffer for two tables (Customer, Invoice). This would be causing an error at runtime. You need to:

DEFINE VARIABLE bh1 AS HANDLE  NO-UNDO.
DEFINE VARIABLE bh2 AS HANDLE  NO-UNDO.

CREATE BUFFER bh1 FOR TABLE "Customer".
CREATE BUFFER bh2 FOR TABLE "Invoice".

and then

qh:ADD-BUFFER(bh1).
qh:ADD-BUFFER(bh2).

The second issue (your compile error), is because the compiler does not see that you're accessing the table Invoice already. bh2 will only at runtime be known to be a buffer for the Invoice table. So you need to access the Cust-Num field dynamically:

FIND Order WHERE Order.Cust-Num =  bh2::Cust-Num NO-LOCK  NO-ERROR.

Note: You're accessing a single Order by the Cust-Num of the invoice here - I assume, you want to do something like accessing the Order by the Invoice's Order-Num field. That would be a logical mistake, not a syntax error.

However, nothing in your program justifies the need for a dynamic query. That just adds in this case unneeded complexity. You program is not yet iterating the records in the dynamic-query qh - but I assume that's the goal. So this simple static FOR EACH block does the same:

FOR EACH Customer NO-LOCK, EACH Invoice WHERE Invoice.Cust-Num = Customer.Cust- 
 Num NO-LOCK: 

    FIND Order WHERE Order.Cust-Num =  Invoice.Cust-Num NO-LOCK  NO-ERROR.

    IF NOT AVAILABLE Order THEN DO:
        FIND Ref-Call WHERE Ref-Call.Cust-Num = Invoice.Cust-Num NO-LOCK NO-ERROR.
        DISPLAY Ref-Call.Cust-Num.
    END /* NOT AVAILABLE */.
END. /* FOR EACH */

Lastly, here:

DELETE OBJECT bh.
DELETE OBJECT qh.

  1. DELETE OBJECT statements belong by their nature into a FINALLY block.

  2. You need to check validity of the handles before deleting them:

    FINALLY: IF VALID-HANDLE (bh1) THEN DELETE OBJECT bh1. IF VALID-HANDLE (bh2) THEN DELETE OBJECT bh2. IF VALID-HANDLE (qh) THEN DELETE OBJECT qh. END.

Upvotes: 3

Stefan Drissen
Stefan Drissen

Reputation: 3379

Here is a working example - my changes to your code can generally be identified by being in lower case.

DEFINE VARIABLE qh AS HANDLE  NO-UNDO.
DEFINE VARIABLE bhc AS HANDLE  NO-UNDO.
DEFINE VARIABLE bhi AS HANDLE  NO-UNDO.
DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO.

CREATE BUFFER bhc FOR TABLE "Customer".
create buffer bhi for table "Invoice".
CREATE QUERY qh.

cQuery = "FOR EACH Customer NO-LOCK,"
       + "EACH Invoice WHERE Invoice.CustNum = Customer.CustNum no-lock".

qh:SET-BUFFERS(bhc,bhi).
qh:QUERY-PREPARE(cQuery).
qh:QUERY-OPEN().
do while qh:get-next().

  message bhc::CustNum bhi::InvoiceNum.
  FIND Order WHERE Order.CustNum = bhi::CustNum NO-LOCK NO-ERROR.
  IF NOT AVAILABLE Order THEN DO:
    FIND RefCall WHERE RefCall.CustNum = bhi::CustNum NO-LOCK NO-ERROR.
    if available RefCall then
      message RefCall.CustNum.
  END.

end.

finally:
   DELETE OBJECT bhc no-error.
   DELETE OBJECT bhi no-error.
   DELETE OBJECT qh no-error.
end finally.

Watch the example run in ABLdojo.

Upvotes: 1

Related Questions