Difference between QueryFilter and QueryBuildRrange

Max Nguyen included in AX2012

January 1, 2017 460 words 3 minutes

Contents

In Dynamics AX, we have two way to filter the result set of records in joined query is using QueryFilter class and QueryBuildRange class.

So what’s difference between them? when do we use QueryFilter class? When do we use QueryBuildRange class?

Today, i will make a simple sample to show what is difference between them. I have a table DuyDang_ParentTable which have 1 columns ID.

/2017-01-01-difference-between-queryfilter-and-querybuildrange/Parent_Table.jpg — Parent_Table

And another table DuyDang_ChildTable which have 3 columns : ID, ParentID, Qty.

/2017-01-01-difference-between-queryfilter-and-querybuildrange/Child_Table.jpg — Child_Table

With the relation DuyDang_ParentTable.ID = DuyDang_ChildTable.ParentID.

I have the Outer Join query:

Use QueryFilter class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
static void DuyDang_QueryFilter(Args _args)
{
    Query query;
    QueryBuildDataSource qbds, qbds1;
    QueryRun queryRun;    
    DuyDang_ParentTable parentTable;
    DuyDang_ChildTable childTable;
    QueryFilter qFilter;
    QueryBuildRange qRange;
    struct structSet;

    structSet = new struct
        ("str ParentID;"
        + "str ChildID;"
        + "real Quantity"
        );

    query = new Query();
    qbds = query.addDataSource(tableNum(DuyDang_ParentTable));  
    qbds1 = qbds.addDataSource(tableNum(DuyDang_ChildTable));    
    qbds1.joinMode(JoinMode::OuterJoin); // Set join type.   
    qbds1.addLink(fieldNum(DuyDang_ParentTable, ID), fieldNum(DuyDang_ChildTable, ParentID));
    qFilter = query.addQueryFilter(qbds1, 'Qty');
    qFilter.value(queryValue(15));
    
    queryRun = new QueryRun(query);    
    while (queryRun.next())
    {
        parentTable = queryRun.get(tableNum(DuyDang_ParentTable));
        childTable = queryRun.get(tableNum(DuyDang_ChildTable));

        structSet.value("ParentID", parentTable.ID);
        structSet.value("ChildID", childTable.ID);
        structSet.value("Quantity", childTable.Qty);
        info(structSet.toString());
    }
}

The result

/2017-01-01-difference-between-queryfilter-and-querybuildrange/Result_Filter.jpg — Child_Table

Use QueryBuildRange class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
static void DuyDang_QueryFilter(Args _args)
{
    Query query;
    QueryBuildDataSource qbds, qbds1;
    QueryRun queryRun;    
    DuyDang_ParentTable parentTable;
    DuyDang_ChildTable childTable;
    QueryFilter qFilter;
    QueryBuildRange qRange;
    struct structSet;

    structSet = new struct
        ("str ParentID;"
        + "str ChildID;"
        + "real Quantity"
        );

    query = new Query();
    qbds = query.addDataSource(tableNum(DuyDang_ParentTable));  
    qbds1 = qbds.addDataSource(tableNum(DuyDang_ChildTable));    
    qbds1.joinMode(JoinMode::OuterJoin); // Set join type.   
    qbds1.addLink(fieldNum(DuyDang_ParentTable, ID), fieldNum(DuyDang_ChildTable, ParentID));
    qRange = qbds1.addRange(fieldNum(DuyDang_ChildTable, Qty));
    qRange.value(queryValue(15));
    
    queryRun = new QueryRun(query);    
    while (queryRun.next())
    {
        parentTable = queryRun.get(tableNum(DuyDang_ParentTable));
        childTable = queryRun.get(tableNum(DuyDang_ChildTable));

        structSet.value("ParentID", parentTable.ID);
        structSet.value("ChildID", childTable.ID);
        structSet.value("Quantity", childTable.Qty);
        info(structSet.toString());
    }
}

and result info here:

/2017-01-01-difference-between-queryfilter-and-querybuildrange/Result_Range.jpg — Child_Table

We can see the difference between them

When you use QueryFilter class, the restriction is in the WHERE clause of the OUTER JOIN in the ANSI SQL select statement that is generated by the AOS for the underlying database system.

1
2
3
4
SELECT * FROM DuyDang_ParentTable(DuyDang_ParentTable_1) 
	OUTER JOIN * FROM DuyDang_ChildTable(DuyDang_ChildTable_1) 
	ON DuyDang_ParentTable.ID = DuyDang_ChildTable.ParentID 
	WHERE ((DuyDang_ChildTable(DuyDang_ChildTable_1).Qty = 15))

When you use QueryBuidRange class, the 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.

1
2
3
4
SELECT * FROM DuyDang_ParentTable(DuyDang_ParentTable_1)
	OUTER JOIN * FROM DuyDang_ChildTable(DuyDang_ChildTable_1) 
	ON DuyDang_ParentTable.ID = DuyDang_ChildTable.ParentID 
		AND ((Qty = 15))

Thank you for reading!