Export (0) Print
Expand All

Select Statement with an Outer Join [AX 2012]

Updated: September 30, 2009

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

In Microsoft Dynamics AX, the X++ SELECT statement supports filtering an OUTER JOIN in the WHERE clause. In the JOIN clause of standard SQL there is an ON keyword for filter criteria. But there is no such ON keyword in X++ SQL.

An inner join rejects all table rows that fail to match a row in the other joined table. But an outer join includes rows from the first table even though there is no matching row in the other joined table. Default values are substituted for the data that could not be obtained from a matching row in the other joined table.

It is possible to filter an outer join at the equivalent of an ON clause that is part of the JOIN clause. For more information, see How to: Use the QueryFilter Class with Outer Joins. For an inner join there is no behavioral difference between filtering on an ON clause versus on the WHERE clause.

This section shows two tables that the code example in this topic relies on. The field types and sample data are included.

There is a 1-to-many relationship between the SalesOrder parent table and the SalesOrderLine child table. There are 0 or more rows in the SalesOrderLine table for each row in the SalesOrder table.

Gg845763.collapse_all(en-us,AX.60).gifSalesOrder Table

There are two rows in the SalesOrder table.

SalesOrderID (integer, primary key)

DateAdded (date)

1

2010-01-01

2

2010-02-02

Gg845763.collapse_all(en-us,AX.60).gifSalesOrderLine Table

The SalesOrderLine table contains a foreign key field, named SalesOrderID, that references the primary key column of the SalesOrder table. The SalesOrderID value 2 does not occur in the data for SalesOrderLine table.

SalesOrderLineID (string, primary key)

Quantity (integer)

SalesOrderID (integer, foreign key)

AA

32

1

BB

67

1

CC

66

1

The following X++ code example has a SELECT statement that reads the tables which are described in the previous section. The SELECT statement includes a left OUTER JOIN clause. The join criteria and the data filter are both on the WHERE clause.


static void OuterJoinSelectJob3(Args _args)
{
    SalesOrder recSalesOrder;
    SalesOrderLine recSalesOrderLine;
    struct struct4;
    ;
    struct4 = new struct
        ("int SalesOrderID;"
        + "date DateAdded;"
        + "str SalesOrderLineID;"
        + "int Quantity"
        );

    while
    SELECT
            *
        from
            recSalesOrder
            OUTER JOIN recSalesOrderLine
        WHERE
            recSalesOrder.SalesOrderID == recSalesOrderLine.SalesOrderID
            && recSalesOrderLine.Quantity == 66
    {
        struct4.value("SalesOrderID", recSalesOrder.SalesOrderID);
        struct4.value("DateAdded", recSalesOrder.DateAdded);
        struct4.value("SalesOrderLineID", recSalesOrderLine.SalesOrderLineID);
        struct4.value("Quantity", recSalesOrderLine.Quantity);
        info(struct4.toString());
    }
}

Gg845763.collapse_all(en-us,AX.60).gifOutput Displayed in the Infolog

This section displays the output from the preceding outer join code example. The second record in the output has a SalesOrderID value of 2. That value of 2 is not present in the SalesOrderLine table. Therefore, some of the fields in the second record have default values, namely 0 for an integer and a zero length string for a string.

(SalesOrderID:1; DateAdded:2010/1/1; SalesOrderLineID:"CC"; Quantity:66)

(SalesOrderID:2; DateAdded:2010/2/2; SalesOrderLineID:""; Quantity:0)


Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. get your copy at the MS Press Store

Community Additions

ADD
Show:
© 2014 Microsoft