Kbase 21711: How to Implement a LEFT-OUTER-JOIN in Progress 4GL
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
27/12/2007 |
|
Status: Verified
GOAL:
How to implement a LEFT-OUTER-JOIN In Progress 4GL
GOAL:
How to do a left join using 4GL
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
FIX:
The INNER-JOIN is the default join between tables unless otherwise specified or coded. The LEFT-OUTER-JOIN is supported only in the OPEN QUERY statement. An outer join returns not only the set of records selected for an inner join, but also returns all the records from the left table.
When a value from the left table( Customer in our example ) does not have a corresponding value in the right table( Order in our example ), Progress returns that left-table value with an unknown value (?) from the right table.
For example the default behavior of a join between the Customer and Order tables is to return only those customers who have orders along with their corresponding Order records. On the other hand a LEFT-OUTER-JOIN result set between these same tables would include all the customer records regardless of whether they have corresponding orders or not.
In the 4GL, you can also implement a RIGHT-OUTER-JOIN by using a LEFT-OUTER-JOIN with the tables in reverse order, but leaving the order of displayed fields the same as for the left outer join. Thus, unknown values from the right side appear on the left side of each displayed row, as if the tables were joined from right to left.
This solution demonstrates the implementation of the default INNER-JOIN, LEFT-OUTER-JOIN and RIGHT-OUTER-JOIN:
/* 1. The Inner Join - The Default Progress Join */
DEFINE VARIABLE iNumResults AS INTEGER NO-UNDO.
DEFINE QUERY qDefaultInnerJoin
FOR customer FIELDS (CustNum),
Order FIELDS (CustNum).
EACH Order WHERE Customer.CustNum = Order.CustNum NO-LOCK.
REPEAT:
GET NEXT qDefaultInnerJoin.
iNumResults = iNumResults + 1.
IF iNumResults = 200 THEN LEAVE.
DISPLAY Customer.CustNum Order.CustNum.
END.
/* 1. The Inner Join - The Default Progress Join */
DEFINE VARIABLE iNumResults AS INTEGER NO-UNDO.
DEFINE QUERY qDefaultInnerJoin
FOR customer FIELDS (CustNum),
Order FIELDS (CustNum).
OPEN QUERY qDefaultInnerJoin FOR EACH Customer,
EACH Order WHERE Customer.CustNum = Order.CustNum NO-LOCK.
REPEAT:
GET NEXT qDefaultInnerJoin.
IF NOT AVAILABLE Customer THEN LEAVE.
iNumResults = iNumResults + 1.
IF iNumResults = 200 THEN LEAVE.
DISPLAY Customer.CustNum Order.CustNum.
END.
/* 2. The Left Outer Join */
DEFINE VARIABLE iNumResults AS INTEGER NO-UNDO.
DEFINE QUERY qCustomerOrders
FOR customer FIELDS (CustNum),
Order FIELDS (CustNum).
OPEN QUERY qCustomerOrders FOR EACH Customer,
EACH Order OUTER-JOIN WHERE Customer.CustNum = Order.CustNum NO-LOCK.
REPEAT:
GET NEXT qCustomerOrders NO-LOCK.
IF NOT AVAILABLE Customer THEN LEAVE.
DISPLAY Customer.CustNum Order.CustNum.
END.
/* 3. The Right Outer Join - A LEFT-OUTER-JOIN with the tables in reverse order */
DEFINE VARIABLE iNumResults AS INTEGER NO-UNDO.
DEFINE QUERY qOrdersCustomer
FOR Order FIELDS (CustNum),
Customer FIELDS (CustNum).
OPEN QUERY qOrdersCustomer FOR EACH Order,
EACH Customer OUTER-JOIN WHERE Order.CustNum = Customer.CustNum NO-LOCK.
REPEAT:
GET NEXT qOrdersCust.omer NO-LOCK.
IF NOT AVAILABLE Order THEN LEAVE.
DISPLAY Customer.CustNum Order.CustNum.
END. .