Article

How to fill a DATASET with an INNER JOIN between tables?

« Go Back

Information

 
Article Number000068053
EnvironmentProduct: OpenEdge
Version: All Supported Versions
OS: All Supported Platforms
Question/Problem Description

When a proDataSet with related tables is filled with data, the default fill behavior is to fill each table with records that fit the criteria specified in each of the DATA-SOURCE objects' queries.  The net result of this behavior is that one may end up with records in a table which is the parent relation to another table that contains no records.

For example:
OPEN QUERY FOR EACH Order, EACH OrderLine WHERE Orderline.OrderNum EQ Order.OrderNum, EACH Item WHERE Item.ItemNum EQ Orderline.LineNum AND Item.Weight EQ 2.

In the above example you would see only Order and Orderline records if there exist Item records which meet the query criteria.  This is default INNER-JOIN behavior.  

However, when a proDataSet is filled the behavior is the same as if an OUTER-JOIN were used.

This behavior is the opposite of the ABL's standard query behavior, however there is no quick and easy option built into the proDataSet hierarchy which will enforce only fetching parent records if there are child records in a relationship which exist in the data source.
 
Steps to ReproduceRun the below code against the Sports2000 database for an example of this.

Note that, when scrolling through the records in the Order browse, there are many records in the Order browse that have no corresponding Item records.
Clarifying Information

DEFINE TEMP-TABLE ttOrder     LIKE Order.
DEFINE TEMP-TABLE ttOrderLine LIKE OrderLine.
DEFINE TEMP-TABLE ttItem      LIKE Item.

DEFINE QUERY qOrder FOR Order.

DEFINE DATA-SOURCE srcOrder     FOR QUERY qOrder Order KEYS (OrderNum).
DEFINE DATA-SOURCE srcOrderLine FOR OrderLine KEYS (OrderNum,LineNum).
DEFINE DATA-SOURCE srcItem      FOR Item KEYS (ItemNum).

DEFINE DATASET dsetOrdLineItem FOR ttOrder, ttOrderLine, ttItem
    DATA-RELATION drelOrdLine FOR ttOrder, ttOrderLine
        RELATION-FIELDS (OrderNum, OrderNum)
    DATA-RELATION drelLineItem FOR ttOrderLine, ttItem
        RELATION-FIELDS (ItemNum, ItemNum).

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE OrderDate GE 01/01/97 AND OrderDate LE 12/31/97").
   
BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE).
BUFFER ttOrderLine:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrderLine:HANDLE).
BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE).

DATA-SOURCE srcItem:FILL-WHERE-STRING  = DATA-SOURCE srcItem:FILL-WHERE-STRING + " AND Weight EQ 2".

DATASET dsetOrdLineItem:FILL().

DEFINE QUERY qOrd  FOR ttOrder     SCROLLING.
DEFINE QUERY qLine FOR ttOrderLine SCROLLING.
DEFINE QUERY qItem FOR ttItem      SCROLLING.

DEFINE BROWSE bOrd QUERY qOrd
    DISPLAY ttOrder.OrderNum
    WITH 10 DOWN.

DEFINE BROWSE bLine QUERY qLine
    DISPLAY ttOrderLine.LineNum
            ttOrderLine.ItemNum
    WITH 10 DOWN.

DEFINE BROWSE bItem QUERY qItem
    DISPLAY ttItem.ItemNum
            ttItem.Weight
    WITH 10 DOWN.

DEFINE FRAME fOrders
    bOrd bLine bItem
    WITH WIDTH 80.

BROWSE bLine:QUERY = DATASET dsetOrdLineItem:GET-RELATION("drelOrdLine"):QUERY.
BROWSE bItem:QUERY = DATASET dsetOrdLineItem:GET-RELATION("drelLineItem"):QUERY.

OPEN QUERY qOrd FOR EACH ttOrder NO-LOCK.
DATASET dsetOrdLineItem:GET-BUFFER-HANDLE("ttOrder"):SYNCHRONIZE().

ON "VALUE-CHANGED" OF BROWSE bOrd DO:
    DATASET dsetOrdLineItem:GET-BUFFER-HANDLE("ttOrder"):SYNCHRONIZE().
END.

ENABLE ALL WITH FRAME fOrders.
WAIT-FOR CLOSE OF THIS-PROCEDURE.

 
Error Message
Defect/Enhancement Number
Cause
Resolution

In order to fill a proDataSet in a fashion resembling an INNER-JOIN, the DATA-SOURCE QUERY for each table involved in the relationship must contain logic similar to the OPEN QUERY statement noted above, starting from the level of the table itself, on down.

The below code demonstrates this:

DEFINE TEMP-TABLE ttOrder     LIKE Order.
DEFINE TEMP-TABLE ttOrderLine LIKE OrderLine.
DEFINE TEMP-TABLE ttItem      LIKE Item.

DEFINE BUFFER bOrdLine  FOR Orderline.
DEFINE BUFFER bOrdItem  FOR Item.
DEFINE BUFFER bLineItem FOR Item.

DEFINE QUERY qOrder   FOR Order, bOrdLine, bOrdItem SCROLLING.
DEFINE QUERY qOrdLine FOR Orderline, bLineItem      SCROLLING.

DEFINE DATA-SOURCE srcOrder     FOR QUERY qOrder    Order     KEYS(OrderNum), 
                                                    bOrdLine  KEYS(OrderNum, LineNum), 
                                                    bOrdItem  KEYS(ItemNum).
DEFINE DATA-SOURCE srcOrderLine FOR QUERY qOrdLine  Orderline KEYS(OrderNum, LineNum), 
                                                    bLineItem KEYS(ItemNum).
DEFINE DATA-SOURCE srcItem      FOR Item                      KEYS (ItemNum).

DEFINE DATASET dsetOrdLineItem FOR ttOrder, ttOrderLine, ttItem
    DATA-RELATION drelOrdLine FOR ttOrder, ttOrderLine
        RELATION-FIELDS (OrderNum, OrderNum)
    DATA-RELATION drelLineItem FOR ttOrderLine, ttItem
        RELATION-FIELDS (ItemNum, ItemNum).

/* Note that the top level query contains a predicate for each table below it in the relationship hierarchy */
QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE OrderDate GE 01/01/97 AND OrderDate LE 12/31/97" +
                           ", EACH bOrdLine WHERE bOrdLine.OrderNum EQ Order.OrderNum" + 
                           ", EACH bOrdItem WHERE bOrdItem.ItemNum EQ bOrdLine.ItemNum AND " +
                           "bOrdItem.Weight EQ 2").

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE).
BUFFER ttOrderLine:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrderLine:HANDLE).
BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE).

QUERY qOrdLine:QUERY-PREPARE("FOR EACH Orderline " + DATA-SOURCE srcOrderLine:FILL-WHERE-STRING + 
                             ", EACH bLineItem WHERE bLineItem.ItemNum EQ Orderline.ItemNum AND " +
                             "bLineItem.Weight EQ 2").
DATA-SOURCE srcItem:FILL-WHERE-STRING  = DATA-SOURCE srcItem:FILL-WHERE-STRING + " AND Weight EQ 2".

DATASET dsetOrdLineItem:FILL().

DEFINE QUERY qOrd  FOR ttOrder     SCROLLING.
DEFINE QUERY qLine FOR ttOrderLine SCROLLING.
DEFINE QUERY qItem FOR ttItem      SCROLLING.

DEFINE BROWSE bOrd QUERY qOrd
    DISPLAY ttOrder.OrderNum
            ttOrder.OrderDate
    WITH 10 DOWN.

DEFINE BROWSE bLine QUERY qLine
    DISPLAY ttOrderLine.OrderNum 
            ttOrderLine.LineNum
            ttOrderLine.ItemNum
    WITH 10 DOWN.

DEFINE BROWSE bItem QUERY qItem
    DISPLAY ttItem.ItemNum
            ttItem.Weight
    WITH 10 DOWN.

DEFINE FRAME fOrders
    bOrd bLine bItem
    WITH WIDTH 80.

BROWSE bLine:QUERY = DATASET dsetOrdLineItem:GET-RELATION("drelOrdLine"):QUERY.
BROWSE bItem:QUERY = DATASET dsetOrdLineItem:GET-RELATION("drelLineItem"):QUERY.

OPEN QUERY qOrd FOR EACH ttOrder NO-LOCK.
DATASET dsetOrdLineItem:GET-BUFFER-HANDLE("ttOrder"):SYNCHRONIZE().

ON "VALUE-CHANGED" OF BROWSE bOrd DO:
    DATASET dsetOrdLineItem:GET-BUFFER-HANDLE("ttOrder"):SYNCHRONIZE().
END.

ENABLE ALL WITH FRAME fOrders.
WAIT-FOR CLOSE OF THIS-PROCEDURE.

 
Workaround
Notes
Attachment 
Last Modified Date4/11/2017 6:27 PM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025