Kbase P124992: ABL/4GL: How to workaround using the CAN-FIND function within an OPEN QUERY statement?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/8/2009 |
|
Status: Verified
GOAL:
ABL/4GL: How to workaround using the CAN-FIND function within an OPEN QUERY statement?
GOAL:
How to avoid using the CAN-FIND function in an OPEN QUERY statement?
GOAL:
How to remove the CAN-FIND function from an OPEN QUERY statement without loss of functionality?
GOAL:
How to replace the illegal use of the CAN-FIND function in an OPEN QUERY statement with a legal User Defined Function ( UDF ) call?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
Progress 4GL/ABL does not allow the use of CAN-FIND within an OPEN-QUERY statement or a QUERY-OPEN( ) method. Thus attempting to run code similar to the following procedure generates the run time error: CAN-FIND is invalid within an OPEN QUERY. (3541)
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE QUERY qCustomer FOR Customer.
OPEN QUERY qCustomer FOR EACH customer WHERE CAN-FIND(Order WHERE Order.Custnum = ipi AND Order.SalesRep = "BBB").
GET FIRST qCustomer NO-LOCK.
DO WHILE AVAILABLE(Customer):
DISPLAY Custnum Country City.
GET NEXT qCustomer NO-LOCK.
END.
To overcome this limitation, we need to remove the CAN-FIND clause from the OPEN QUERY statement. The following example, demonstrates how to replace the illegal use of the CAN-FIND clause from the OPEN QUERY statement with a User Defined Function ( UDF ) call without any loss of the desired functionality:
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE QUERY qCustomer FOR Customer.
OPEN QUERY qCustomer FOR EACH customer.
GET FIRST qCustomer NO-LOCK.
ahoma size=1>DO WHILE AVAILABLE(Customer):
lResult = DYNAMIC-FUNCTION('canFindIt', INPUT Customer.Cust-Num).
IF lResult THEN
DISPLAY Cust-num Country City.
GET NEXT qCustomer NO-LOCK.
END.
FUNCTION canFindIt RETURNS LOGICAL(INPUT gipi AS INTEGER):
RETURN CAN-FIND(FIRST Order WHERE Order.Cust-num = gipi AND Order.Sales-Rep = "BBB").
END FUNCTION.
Another example is to move the CAN-FIND clause from the OPEN QUERY statement to a session FIND trigger:
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE QUERY qCustomer FOR Customer.
/* Activate the FIND trigger that filters the result set using the CAN-FIND condition */
ON FIND OF customer DO:
IF NOT CAN-FIND(Order WHERE Order.Custnum = Customer.Custnum AND Order.SalesRep = "BBB") THEN RETURN ERROR.
END.
OPEN QUERY qCustomer FOR EACH customer.
GET FIRST qCustomer NO-LOCK.
NT face=Tahoma>DO WHILE AVAILABLE(Customer):
DISPLAY Custnum Country City.
GET NEXT qCustomer NO-LOCK.
END.
/* Deactivate the FIND trigger that filters the result set using the CAN-FIND condition */
ON FIND OF customer REVERT.
Another alternative to the use of the CAN-FIND clause, is the use of an inner join. For example:
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE QUERY qCustomer FOR Customer, Order.
OPEN QUERY qCustomer FOR EACH Customer, FIRST Order WHERE Order.Cust-num = Customer.Cust-num AND Order.Sales-Rep = "BBB".
GET FIRST qCustomer NO-LOCK.
DO WHILE AVAILABLE(Customer):
DISPLAY Customer.Cust-num Customer.Country Customer.City.
GET NEXT qCustomer NO-LOCK.
END..