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 two table :
Table DuyDang_ParentTable
which have 1 columns : ID
.

Table DuyDang_ChildTable
which have 3 columns : ID
, ParentID
, Qty
.

and they have relation like this : 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
38
39
|
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));
//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:

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
38
39
|
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));
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:

now you can see the difference between them
- 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))
|
- 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))
|
Thank you for reading!