Using Select Statements as Expressions in X++

In X++, you can write select statements as expressions, known as expression select statements. These are different from traditional select statements in several important ways and are typically used to fetch a single value directly.

Key Characteristics:

No Table Buffers: You cannot use a table buffer variable; instead, the actual table name must appear in the from clause.
No Joins: The join keyword is not supported, and you cannot use subselects as a workaround. Only one table can be used in an expression select.
Field Qualification Rules:
- In the order by clause, field names must not be prefixed with the table name.
- In the where clause, field names must be qualified using the table name.
Field Selection Limitation: Only the column specified before the from clause will return a value.
After the select (...) expression, use .FieldName to access the returned value.

Example

Fetch First Row’s Account Number (Descending Order)

				
					str accountNum = (select AccountNum from CustTable order by AccountNum desc).AccountNum;
info('Max AccountNum: ' + accountNum);

2. Simplified Version Using Aggregate Function

				
					str accountNum = (select maxof(AccountNum) from CustTable).AccountNum;
info('Max AccountNum: ' + accountNum);

3. Get Max RecId of Unblocked Customers

Here, we use an aggregate function (maxof) in combination with a where condition. Make sure the field referenced after the statement matches the one used inside the aggregate function.

				
					int64 nRecId = (select maxof(RecId) from CustTable
            where CustTable.Blocked == CustVendorBlocked::No).RecId;
info("Max RecId: " + int642Str(nRecId));

4. Count Unblocked Customers

Although the field name used after the select is RecId, it actually returns a count because count(RecId) was used. This is a common pattern.

				
					int64 nRecId = (select count(RecId) from CustTable
            where CustTable.Blocked == CustVendorBlocked::No).RecId;
info('Count of unblocked customers: ' + int642Str(nRecId));

Using Select Statements on Fields

You can also use select statements in expressions to retrieve a specific field from a record. These are often used in conditions or output operations.

Key Difference:

Field Select: Directly operates on the table.
Regular Select: Uses a table buffer.

Example 1 – Print a Field:

				
					print((select CustTable order by AccountStatement).AccountStatement);

Example 2 – Use in Conditional Logic:

				
					if ((select CustTable where CustTable.AccountNum == '3000').CreditMax < 5000)
{
    info('This customer has a credit maximum less than $5000.');
}