How to fill a Dataset with an inner join between tables?

Owned by Mike Fechner
Apr 14, 2016
5 min read

Original location of this article

This article was initially released in the Progress K-Base: http://knowledgebase.progress.com/articles/Article/How-to-fill-a-DATASET-with-an-INNER-JOIN-between-tables

Article Number

Article Number 000068053

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. A proDataSet is filled essentially using an OUTER-JOIN.

This behavior is the antithesis 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 Reproduce

Run 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.

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.