Query Ranges in X++
Expressions
in query ranges
One of
least understood but most powerful Axapta features is the so-called Expressions
in query ranges syntax.
This is not the same
as simply using a QueryBuildRange object in a query and specifying a criteria
for a single field.
Introduction
This is a
method of specifying ranges on queries which allows you to perform complex
comparisons, and create complex join situations which would be impossible using
the standard syntax.
Syntax
To use the
special syntax, you should first add a range to your QueryBuildDataSource object
in the normal way. Note that for this special syntax, it does not matter which
field you use to add the range.
To specify
the range value itself, certain rules must be followed:
§ The entire
expression must be enclosed within single-quotes, not double-quotes
§ The entire
expression must be enclosed in parenthesis (brackets)
§ Each
sub-expression must be enclosed in its own set of parenthesis
§ For fields
in the current table, simply the field name can be used
§ For fields
in other tables, a prefix of the relevant datasource name must be added. This is not always the same
as the table name.
§ String
values should be surrounded by double-quotes, and wrapped in a call to
queryValue()
§ Enum
values should be specified by their integer value
§ Date
values should be formatted using Date2StrXpp()
§ Blank
string like ' ' will not work as expected, use
sysquery::valueEmptyString().
Examples
In the
example below, we construct a query and add a single datasource.
The range
is then added, using the DataAreaId field on each table. Any field can be used,
but using an unusual one such as DataAreaId helps remind a casual reader of the
code that it's not a normal range.
query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable));
// Add our range
queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
Given the
above, the following are valid range specifications:
Simple criteria
Find the
record where ItemId is B-R14. Take note of the single quotes and parenthesis
surrounding the entire expression.
queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("B-R14")));
Find
records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));
Find
records where the ItemType is Service or the ItemId is B-R14. Note the nesting of the
parenthesis in this example.
queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))',
any2int(ItemType::Service),
queryValue("B-R14")));
Find
records where the modified date is after 1st January 2000. Note the use of
Date2StrXpp() to format the date correctly.
queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));
Find
records where the Field is blank (null) or an empty string. For more see
Sys::Query Docs
qbrStatement = this.query().dataSourceName("BankAccountTrans2").addRange(fieldnum(BankAccountTrans,AccountStatement));
//qbrStatement.value("!?*");//this is the old way that may not work in future versions of AX
qbrStatement.value(sysquery::valueEmptyString());//this is the new way
Complex criteria with combined AND and OR clauses
Find all
records where the ItemType is Service, or both the ItemType is Item and the
ProjCategoryId is Spares. This is not possible to achieve using the standard
range syntax.
Note also
that in this example, we are using the fieldStr() method to specify our actual
field names and again, that we have nested our parenthesis for each
sub-expression.
queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',
fieldStr(InventTable, ItemType),
any2int(ItemType::Service),
any2int(ItemType::Item),
fieldStr(InventTable, ProjCategoryId),
queryValue("Spares")));
WHERE clauses referencing fields from multiple tables
For this
example below, we construct a query consisting of two joined datasources (using
an Exists join). Note that we specify the datasource names when adding the
datasources to the query.
The ranges
are then added, using the DataAreaId field on each table as described in the
earlier example.
query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), tableStr(InventTable));
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), tableStr(InventItemBarCode));
dsInventItemBarCode.relations(true);
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
Find all
records where a bar code record exists for an item and was modified later than
the item was modified.
In this
example, we are using the range on the BarCode table. Therefore the unqualified
ModifiedDate reference will relate to InventItemBarCode.ModifiedDate. The other
field is a fully-qualified one, using the DatasourceName.FieldName
syntax.
queryBuildRange2.value(strFmt('(ModifiedDate > InventTable.ModifiedDate)'));
Note that
if we had added our InventTable datasource using the following code
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTableCustomName"); // Note that we are manually specifying a different datasource name
then the
query range would need to appear as follows
queryBuildRange2.value(strFmt('(ModifiedDate > InventTableCustomName.ModifiedDate)'));
Conditional joins
We will
modify our previous example slightly, to remove the automatic addition of
relations for the join.
query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTable");
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), "InventItemBarCode");
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
We can now
use the query expression to specify whatever we like as the join
criteria.
Find all
records where either the ItemType is Service, or the ItemType is Item and a
barcode exists. The join criteria is only applied in the second half of the
expression, so all Service items will appear irrespective of whether they have a
bar code. Again, this is not possible to achieve using the standard query
ranges.
queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',
query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1
fieldStr(InventTable, ItemType), // ItemType %2
any2int(ItemType::Service), // %3
any2int(ItemType::Item), // %4
fieldStr(InventTable, ItemId), // ItemId %5
fieldStr(InventItemBarCode, ItemId))); // %6
Using the
techniques above, it is possible to create queries with almost as much
flexibility as using SQL statements directly.
Filter
on array fields
queryBuildRange.value(strFmt('((%1.%2 == "%4") || (%1.%3 == "%5"))',
queryBuildDataSource.name(),
fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Center))),
fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Purpose))),
"some dim2 value",
"some dim3 value"));
Note: you must always
specify the datasource name if you use Query Expression syntax to filter on
array fields. See also Limitations section at the bottom of the
page.
Using wildcards and comma-separated range values
Again, the
previous example here was using standard syntax, not the special syntax using
expressions. It's not possible to modify the above examples to work with
wildcards.
The above
statement applies to AX versions < 5.0
AX 5.0
introduced solution to wildcards - while you still cannot directly use wildcards
in ranges, now it supports the 'LIKE' keyword.
(AccountNum LIKE "*AA*" || Name LIKE "*AA*")
