Writing a Select Statement as an Expression

Dynamics AX 2009

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 shows some limitations of expression selects.



join keyword is not supported

The join keyword is not supported in an expression join.

Test_3 in the code sample shows that a subselect is supported, but only in a limited way.

The following table describes the test cases in the code sample.

Test case



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.


This is a simpler way to achieve the same result as Test_1.a.


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.


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.


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.
    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,
    str sAccountNum,
    // Test_1.a
    sAccountNum = (select firstonly AccountNum from CustTable
        order by AccountNum desc
        where 0 == 0 // 'where' must occur after 'order by'.
    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"
    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

Community Additions