Writing a Select Statement as an Expression [AX 2012]
Updated: November 24, 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, you can use an X++ SQL select statement as an expression. This is called an expression select. A table buffer variable cannot be used in an expression select statement. The name of the table must be used in the from clause.
The following table describes the test cases in the code sample.
|
Test case |
Description |
|---|---|
|
Test_1.a |
The select statement inside the parentheses returns one row. The only column that can be populated with data is the column that is named in the select clause before the from clause. The name of that one column is used after the closing parenthesis to reference the data value: ).AccountNum;. This test case returns a maximum of one row because it uses the firstonly keyword. However, the value that is assigned to sAccountNum is the same even if the firstonly keyword is omitted. The where clause in this example serves no purpose other than to show that the where clause must occur after the order by clause. The table name cannot be used to qualify a field name in the order by clause. |
|
Test_1.b |
This is a simpler way to achieve the same result as Test_1.a. |
|
Test_2.c |
Includes a where clause. In a where clause, the table name must be used as a qualifier of the field. Here the maxof aggregate function is used, and the field RecId is mentioned in the function. The field that is mentioned in the aggregate function must be the same field name that is used to reference the data value after the closing parenthesis. Otherwise, empty data is returned. |
|
Test_2.d |
Demonstrates that a field name, here RecId, is used to reference a data value that is not a RecId. The count aggregate function does not return a RecId value. The RecId field is ordinarily used with the count function. |
|
Test_3 |
The join keyword is not supported in expression selects. This test case demonstrates a subselect. But expression selects do not support subselects that are equivalent to a standard inner join. For instance, the following X++ SQL does not compile. The problem is that it mentions two tables inside one expression select, namely inside the subselect.
// X++. This job does not compile.
static void BadJob86(Args _args)
{
// Test_3.f
sName = (select Name from AssetTable
where AssetTable.AssetId ==
// Here starts the subselect.
(select AssetId from AssetTrans
where AssetTrans.AssetId ==
AssetTable.AssetId // Compiler rejects this line.
).AssetId).Name;
info(strFmt("Test_3: %1", sName));
}
|
Code Sample
The following code sample demonstrates several expression selects. Its test cases are described in the previous table.
// X++
static void SelectAsExpression3Job(Args _args)
{
int64 nRecId,
nCount;
str sAccountNum,
sName;
;
// Test_1.a
sAccountNum = (select firstonly AccountNum from CustTable
order by AccountNum desc
where 0 == 0 // 'where' must occur after 'order by'.
).AccountNum;
info(strFmt("Test_1.a: %1", sAccountNum));
// Test_1.b
sAccountNum = (select maxof(AccountNum) from CustTable).AccountNum;
Global::info(strFmt("Test_1.b: %1", sAccountNum));
// Test_2.c
nRecId = (select maxof(RecId) from CustTable
where CustTable.Blocked == CustVendorBlocked::No).RecId;
info(strFmt("Test_2.c: %1", nRecId));
// Test_2.d
nRecId = (select count(RecId) from CustTable
where CustTable.Blocked == CustVendorBlocked::No).RecId;
info(strFmt("Test_2.d: %1", nRecId));
// Test_3
sName = (select Name from AssetTable
where AssetTable.AssetId ==
(select AssetId from AssetTrans
where AssetTrans.AssetId == "CNC-01"
).AssetId).Name;
info(strFmt("Test_3: %1", sName));
}
The output from a run of the previous code sample is shown in the following table.
|
Output to Infolog |
|---|
|
Test_1.a: 4507 Test_1.b: 4507 Test_2.c: 5637144604 Test_2.d: 29 Test_3: CNC-Metal shade |
Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.