Replacing NOT CAN-FIND with a join

iponomarenko

iponomarenko

New Member
Hi,

I'm a progress newbie, I would like to get all records from table1 where there are no corresponding records in table2. something like all customers who don't have any orders.

this is easy with a FOR EACH customer and a NOT CAN-FIND (FIRST order WHERE order.customerID EQ customer.customerID).

but I want to do this in a query (QUERY-PREPARE statement), and CAN-FIND is not allowed, the error message says I'm supposed to use joins instead, but I can't figure out how to do this.

Anyone can help ?

Thanks !
 
It is something with OUTER-JOINS, but I cannot at the moment get the NOT CAN-FIND part to skip the records you do not want, so some extra preprocessing is required within the loop:

Code: 
DEFINE TEMP-TABLE tt1 NO-UNDO
   FIELD ii AS INTEGER
INDEX tt1ix IS UNIQUE PRIMARY ii.
DEFINE TEMP-TABLE tt2 NO-UNDO
   FIELD ii AS INTEGER
INDEX tt2ix IS UNIQUE PRIMARY ii.
CREATE tt1. tt1.ii = 1.
CREATE tt1. tt1.ii = 2.
CREATE tt1. tt1.ii = 4.
CREATE tt2. tt2.ii = 2.
CREATE tt2. tt2.ii = 3.
DEFINE VARIABLE hq AS HANDLE NO-UNDO.
CREATE QUERY hq.
hq:SET-BUFFERS( TEMP-TABLE tt1:DEFAULT-BUFFER-HANDLE, TEMP-TABLE tt2:DEFAULT-BUFFER-HANDLE ).
hq:QUERY-PREPARE( "FOR EACH tt1, FIRST tt2 OUTER-JOIN WHERE tt2.ii = tt1.ii" ).
hq:QUERY-OPEN().
DO WHILE hq:GET-NEXT():
   
   MESSAGE 
      IF AVAILABLE tt1 THEN tt1.ii ELSE "-" SKIP
      IF AVAILABLE tt2 THEN tt2.ii ELSE "-"
   VIEW-AS ALERT-BOX.
END.
DELETE OBJECT hq.
 
Thanks, thats a good start, I was hoping to be able to do something like this :
"FOR EACH tt1, FIRST tt2 LEFT OUTER-JOIN WHERE tt2.ii EQ tt1.ii AND tt2.ii EQ ?" but it doesn't work, it returns all records from tt1 anyway.
However I can test in the loop if tt2.ii EQ ?, I guess it's a two step procedure.
 
You can use another join to test availability of record. TT3 must have at least one record - you can use temp-table with one record or any non-empty database table (probably small table can be slightly faster than big one).
Code: 
FOR EACH tt1, 
     FIRST tt2 OUTER-JOIN WHERE tt2.ii EQ tt1.ii,

     FIRST tt3 WHERE NOT AVAIL tt2
 
Thanks - that's the one I was looking for. I had attempted a ,FIRST btt1 WHERE btt1.ii <> tt2.ii and that was not working (since it was nonsense...). Scanned the help file too quickly and read the note:

Note:
If you specify the OUTER-JOIN option, you must also specify the OUTER-JOIN option in all succeeding Record phrases of the query to obtain a left outer join. That is, for multiple Record phrases, all joins in the query following your first left outer join must also be left outer joins. Otherwise, the result is an inner join for all records up to the last inner join in the query. For more information, see OpenEdge Development: ABL Handbook.
Click to expand...

As meaning that ANYTHING after an outer-join would be handled as an outer-join and decided to give up.

So complete example (using a buffer on original 'table'):

Code: 
DEFINE TEMP-TABLE tt1 NO-UNDO
   FIELD ii AS INTEGER
INDEX tt1ix IS UNIQUE PRIMARY ii.
DEFINE TEMP-TABLE tt2 NO-UNDO
   FIELD ii AS INTEGER
INDEX tt2ix IS UNIQUE PRIMARY ii.
 
CREATE tt1. tt1.ii = 1.
CREATE tt1. tt1.ii = 2.
CREATE tt1. tt1.ii = 4.
CREATE tt2. tt2.ii = 2.
CREATE tt2. tt2.ii = 3.
 
DEFINE VARIABLE hbtt1 AS HANDLE NO-UNDO.
DEFINE VARIABLE hq AS HANDLE NO-UNDO.
 
CREATE QUERY hq.
CREATE BUFFER hbtt1 FOR TABLE "tt1" BUFFER-NAME "btt1".
hq:SET-BUFFERS( TEMP-TABLE tt1:DEFAULT-BUFFER-HANDLE, TEMP-TABLE tt2:DEFAULT-BUFFER-HANDLE, hbtt1).
hq:QUERY-PREPARE( "FOR EACH tt1, FIRST tt2 OUTER-JOIN WHERE tt2.ii = tt1.ii, FIRST btt1 WHERE NOT AVAILABLE tt2" ).
hq:QUERY-OPEN().

DO WHILE hq:GET-NEXT():   
   MESSAGE tt1.ii VIEW-AS ALERT-BOX.
END.

DELETE OBJECT hq.
 
Hello,
well although this solution works, performance wise it is much slower then NOT CAN-FIND.
So, I had to create a temp table, and populate it with the For Each, and then use the query on the temp table.
 
You must log in or register to reply here.
Back
Top