How to: Use the QueryFilter Class with Outer Joins

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 QueryFilter class gives you a way to filter the result set of an outer join to obtain a different outcome than you can obtain from X++ SQL. The QueryFilter class applies the filters to the WHERE clause in standard SQL, instead of to the ON keyword of the JOIN clause. Filters that are specified on the WHERE clause are applied later in the process than filter on the ON keyword. The Application Object Server (AOS) sends standard SQL to the underlying database management system. Applying the filters on the WHERE clause can produce a result set that contains no records that are built from default values.

As an alternative way to filter an outer join query, you can use the QueryBuildRange class. It produces results that match what you get from X++ SQL with an OUTER JOIN. For more information, see Select Statement with an Outer Join.

The present topic contains the following sections:

• Tables and Test Data for this Demonstration

• Code Example with the QueryFilter Class

• Code Example with the QueryBuildRange Class

• ANSI SQL Representations

Tables and Test Data for this Demonstration

This section shows two tables that the code examples in this topic rely 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.

SalesOrder Table

There are two rows in the SalesOrder table.

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

Code Example with the QueryFilter Class

The following X++ code example uses the QueryFilter class to filter the result set from an outer join.

The QrySalesAndLines query joins the parent and child tables on an equality test between parent's primary key field and child's foreign key field. The following job adds another join filter criteria which tests whether the Quantity field equals 66.

    // X++
    static void QueryFilter4fJob(Args _args)
    {
        Query query2;
        QueryBuildDataSource qbDataSource3;
        QueryRun queryRun4;
    
        // The QueryFilter class was not in Microsoft Dynamics AX 2009.
        QueryFilter qFilter7;
    
        SalesOrder recSalesOrder;
        SalesOrderLine recSalesOrderLine;
    
        struct struct5;
    
        struct5 = new struct
            ("int SalesOrderID;"
            + "date DateAdded;"
            + "str SalesOrderLineID;"
            + "int Quantity"
            );
    
        query2 = new Query("QrySalesAndLines");
        qbDataSource3 = query2.dataSourceName("SalesOrderLine_1");
    
        // This QueryFilter restriction is in the WHERE clause of
        // the ANSI SQL Select statement that is generated by the AOS
        // for the underlying database system.
        qFilter7 = query2.addQueryFilter(qbDataSource3, "Quantity");
        qFilter7.value("66");
    
        queryRun4 = new QueryRun(query2);
        while (queryRun4.next())
        {
            recSalesOrder = queryRun4.getNo(1);
            recSalesOrderLine = queryRun4.getNo(2);
    
            struct5.value("SalesOrderID", recSalesOrder.SalesOrderID);
            struct5.value("DateAdded", recSalesOrder.DateAdded);
            struct5.value("SalesOrderLineID", recSalesOrderLine.SalesOrderLineID);
            struct5.value("Quantity", recSalesOrderLine.Quantity);
            info(struct5.toString());
        }
    }

The output results do not contain any records that are built with default values.

Code Example with the QueryBuildRange Class

The following X++ code example uses the QueryBuildRange class to filter the result set from an outer join.

The QrySalesAndLines query joins the parent and child tables on an equality test between parent's primary key field and child's foreign key field. The following job adds another join filter criteria which tests whether the Quantity field equals 66.

    // X++
    static void QueryBuildRange5rJob(Args _args)
    {
        Query query2;
        QueryBuildDataSource qbDataSource3;
        QueryRun queryRun4;
        QueryBuildRange qbRange6;
    
        SalesOrder recSalesOrder;
        SalesOrderLine recSalesOrderLine;
    
        struct struct5;
        ;
        struct5 = new struct
            ("int SalesOrderID;"
            + "date DateAdded;"
            + "str SalesOrderLineID;"
            + "int Quantity"
            );
    
        query2 = new Query("QrySalesAndLines");
        qbDataSource3 = query2.dataSourceName("SalesOrderLine_1");
    
        // This QueryBuildRange restriction is in the ON clause of
        // the OUTER JOIN in the ANSI SQL Select statement that is
        // generated by the AOS for the underlying database system.
        qbRange6 = qbDataSource3.addRange
            (fieldNum(SalesOrderLine, Quantity));
        qbRange6.value("66");
    
        queryRun4 = new QueryRun(query2);
        while (queryRun4.next())
        {
            recSalesOrder = queryRun4.getNo(1);
            recSalesOrderLine = queryRun4.getNo(2);
    
            struct5.value("SalesOrderID", recSalesOrder.SalesOrderID);
            struct5.value("DateAdded", recSalesOrder.DateAdded);
            struct5.value("SalesOrderLineID", recSalesOrderLine.SalesOrderLineID);
            struct5.value("Quantity", recSalesOrderLine.Quantity);
            info(struct5.toString());
        }
    }

The output results do contain a record that is built with default values.

ANSI SQL Representations

The following two standard ANSI SQL code examples correspond to the filter approaches of the QueryFilter class and the QueryBuildRange class.

ANSI SQL from QueryFilter

The following SQL SELECT statement returns the same one row that the preceding QueryFilter class example returns.

-- ANSI SQL
SELECT
    from
        SalesOrder as tSO

        LEFT OUTER JOIN
        SalesOrderLine as tSOL
            ON
                (tSO.SalesOrderID = tSOL.SalesOrderID
                )
    where
        tSOL.Quantity = 66
;

ANSI SQL from QueryBuildRange

The following SQL SELECT statement returns the same two rows that the preceding QueryBuildRange class example returns.

-- ANSI SQL
SELECT
    from
        SalesOrder as tSO

        LEFT OUTER JOIN
        SalesOrderLine as tSOL
            ON
                (tSO.SalesOrderID = tSOL.SalesOrderID
                and
                tSOL.Quantity = 66
                )
;

See also

Queries in the AOT for Data Access

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