Convert SQL to Progress (Outer Join)

[ChezJfrey]

I don't seem to have a grasp of the Progress syntax required to form the equivilant of this:

SELECT A.Field1, A.Field2, B.Field1, B.Field2
FROM A LEFT JOIN B ON A.Field1 = B.Field1 AND A.Field2 = B.Field2
WHERE B.Field1 IS NULL

Anyone care to help?

 

[cup99]

Try this:

for each a no-lock:
for each b no-lock
where a.field1 = b.field1
and a.field2 = b.field2
and b.field1 = ?:

display a.field1 a.field2 b.field1 b.field2.
end.

If the index fields are in both tables you should be able to use:

for each a no-lock:
for each b of a no-lock
where field1 = ?:

display a.field1 a.field2 b.field1 b.field2.
end.

I hope this has help.

 

[ChezJfrey]

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?

 

[ChezJfrey]

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.

 

[mra]

You want to do something if data is in DB1.MyTable but not in DB2.MyTable - Am I correct?

Try this:

for each DB1.MyTable where .....:
if not can-find( first DB2.MyTable where <Join to DB1.Mytable>)
do:
display "Not in DB2.MyTable".
end.
end.

can-find is faster, beacuse Pogress doesn't retreive data, just checks index keys.

For even faster performance.
Use No-Lock whenever possible.
Use the fields clause in "for each" whenever possible.

Regards
Mike

 

[ChezJfrey]

SOLVED

This is what I want, thanks.