Kbase P71959: 4GL or ADM2: How to achieve a sophisticated query, such as FOR EACH parent WITHOUT child
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/1/2004 |
|
Status: Unverified
GOAL:
How to achieve a WITHOUT option in a query, such as: FOR EACH parent WITHOUT child (FOR EACH customer WITHOUT order)
GOAL:
Sample code to build a complex filter condition in a QUERY thanks to a FIND Trigger
GOAL:
How to achieve a QUERY to fetch the records of a table that have no INNER table record available
FIX:
In straight 4GL without query, you achieve it with:FOR EACH parent:
FIND FIRST child OF parent NO-ERROR.
IF AVAIL child THEN NEXT.
< your logic here >
END.Now, one can imagine situations that require to achieve that with a query (especially with SDO's). However, at the time of writing (9.1D), there is no straight way to achieve something like FOR EACH customer WITHOUT order , and the following QUERY does not work as wanted:
FOR EACH customer WHERE ROWID(order) = ?, FIRST order OF customer OUTER-JOIN
Now it is possible to achieve this kind of query by taking advantage of the FIND trigger.
The idea is to create an ad-hoc FIND trigger that returns an error when the complex filtering condition is not matched, so the QUERY will skip an unwanted record and carry on with others.
Here is a find trigger to achieve the above example:
/* example1.p*/
ON FIND OF customer /*Note the scope of this trigger is the all SESSION
if example1.p is running persistently */
DO:
IF CAN-FIND(FIRST order WHERE order.custNum = customer.custNum) THEN
RETURN ERROR.
END.
OPEN QUERY q
FOR EACH customer.
DEF BROWSE myBrowse QUERY q
DISPLAY custnum NAME WITH 10 DOWN.
UPDATE myBrowse.Now, be aware that the scope of such a trigger is THE ALL 4GL SESSION, so you might want to achieve the two following points:
1) Implement this trigger in a separate PERSISTENT procedure so you can load it in memory whenever you need to, and kill it once you don't.
2) Implement a safe guard mechanism in the trigger to be sure that other queries or other FIND's (possibly from other procedures) are not using this complex filtering without notice. This safe guard can easily be achieved by calling a User Defined Function in the TARGET-PROCEDURE of the trigger (the one that fires the WRITE trigger). In the example bellow, this function is called 'DoYouWantCustomerWithoutOrders'
/* occasionalCustFindTrig.p */
ON FIND OF customer
DO:
DEFINE VARIABLE lWantCustWihoutOrders AS LOGICAL NO-UNDO.
lWantCustWihoutOrders =
DYNAMIC-FUNCTION('DoYouWantCustomerWithoutOrders':U IN TARGET-PROCEDURE)
NO-ERROR.
/* If the function exists, and if it says yes, then check that the customer
has no order */
IF lWantCustWihoutOrders THEN DO:
/* lWantCustWihoutOrders is unknown if the function was not available in
the target proc, so in this case, we don't reach this point*/
IF CAN-FIND(FIRST order OF customer) THEN RETURN ERROR.
END.
/* otherwise just do normal job, i.e. nothing */
/* Or you may want to implement a killYourself feature to kill this trigger
procedure for performance sake
Then the objects/procedures that really need this trigger could check if
it is running before playing with a QUERY
=> add a procedure called "isOccasionalCustFindTrigRunning" in
occasionalCustFindTrig.p, with a logical OUTPUT parameter to return YES
and do a SUBSCRIBE TO "isOccasionalCustFindTrigRunning" ANYWHERE*/
END.
Then run the above procedure persistently before playing with the query (before OPEN, GET-NEXT, REPOSITION...), and kill it afterward. Note that with the safeguard, you could keep it in memory for the all session without affecting the result of other queries and FIND's of other procedures. But keep in mind that a useless FIND trigger will not help for performance.
In the case of an SDO, you shall manage the run and delete in and override of initializeObject and destroyObject: /* initializeObject override in the SDO */
RUN occasionalCustFindTrig.p PERSIST SET hoccasionalCustFin.dTrig.
RUN SUPER.
END PROCEDURE.
/* destroyObject override in the SDO */
IF VALID-HANDLE(hoccasionalCustFindTrig)
THEN DELETE PROCEDURE hoccasionalCustFindTrig.
RUN SUPER.
END PROCEDURE.As you see the trigger tries to call a function named 'DoYouWantCustomerWithoutOrders' in the TARGET-PROCEDURE (the one that does a FIND or a QUERY:OPEN() or QUERY:GET-NEXT ...), so you have to define it in the TARGET-PROCEDURE. In the case of an SDO, define it in the SDO itself or in its Data Logic Procedure (Dynamics): FUNCTION DoYouWantCustomerWithoutOrders RETURNS LOGICAL
( /* parameter-definitions */ ) :
/* You might even rely on a user defined property to decide whether you
want to return YES or NO.
But for performance sake, you should handle such a property with a variable
defined in the definition block:
RETURN < myPropertyValue_logical_variable >.
=> in other words do not call any get or getUserProperty
function every time here. */
RETURN YES. /* Function return value. */
END FUNCTION.This solution has been tested successfully with simple 4GL in 9.1D08 and with Smart Objects.
.