Thanks. I figured I could use nested statements, but the single SQL statement is quite elegant and was wondering if the left-join phrase used in an open query statement would accomplish the same thing by returning only records in the table with missing records. Or is at least one nested statement required?
Here is a more specific example of my problem. Is there any way to make the Progress code more efficient or faster? I ask because I want to perform a similar query where I insert/delete records based on the result set rather than merely display.
The following SQL statement takes 36 seconds to complete:
SELECT DB1.MyTable.Field1, DB2.MyTable.Field1
FROM DB1.MyTable LEFT JOIN DB2.MyTable ON
DB1.MyTable.Field1 = DB2.MyTable.Field1 AND
DB1.MyTable.Field2 = DB2.MyTable.Field2 AND
DB1.MyTable.Field3 = DB2.MyTable.Field3
WHERE DB2.MyTable.Field1 IS NULL.
The following Progress code takes 147 seconds to complete:
OPEN QUERY qryTest FOR EACH DB1.MyTable,
EACH DB2.MyTable LEFT OUTER-JOIN WHERE
DB1.MyTable.Field1 = DB2.MyTable.Field1 AND
DB1.MyTable.Field2 = DB2.MyTable.Field2 AND
DB1.MyTable.Field3 = DB2.MyTable.Field3
NO-LOCK.
REPEAT:
GET NEXT qryTest.
IF NOT AVAILABLE(DB1.MyTable) THEN
LEAVE.
IF DB2.MyTable.Field1 = ? THEN
DISPLAY DB1.MyTable.Field1 DB2.MyTable.Field1.
END.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.