| name | xpp-database-queries |
| description | Authoritative rules for X++ select / while-select queries in D365 Finance & Operations. Invoke whenever the user asks to write a "select", "while select", "query", joins, aggregates, cross-company, set-based ops, or any data-access X++. |
| applies_when | User intent involves X++ data access — select / while select / joins / aggregates / cross-company / validTimeState / set-based operations. |
⛔ NEVER write X++ AOT XML files directly via PowerShell, terminal file commands (Set-Content, Out-File, New-Item), editor write tools, or any raw text approach. The XML schema (<AxClass>, <AxTable>, <AxForm>, <Methods>, <SourceCode>) is proprietary — LLMs have not been trained on it reliably. ALWAYS use d365fo generate … commands to produce correct AOT XML. If d365fo is unavailable in PATH, stop and ask the user to install it.
X++ database queries — select / while select
Source of truth: learn:xpp-select-statement.
Pre-flight: confirm the target table / field with d365fo get table <Name> --output json before writing the query.
Statement order (grammar-enforced)
select [FindOption…] [FieldList from] tableBuffer [index…]
[order by | group by] [where …] [join … [where …]]
FindOption keywords (crossCompany, firstOnly, forUpdate, forceNestedLoop, forceSelectOrder, forcePlaceholders, pessimisticLock, optimisticLock, repeatableRead, validTimeState, noFetch, reverse, firstFast) sit between select and the buffer / field list — never on a joined buffer (sole exception: forUpdate may target a specific buffer in a join).
order by / group by / where must appear after the LAST join clause — never between joins.
crossCompany belongs on the OUTER buffer
// ✅ CORRECT
select crossCompany custTable
join custInvoiceJour
where custInvoiceJour.OrderAccount == custTable.AccountNum;
// ❌ WRONG — cross-company on the joined buffer
select custTable
join crossCompany custInvoiceJour
where …;
Optional company filter: select crossCompany : myContainer custTable … — myContainer is a container literal (['dat'] + ['dmo']). Empty container = scan all authorised companies.
in operator — any primitive type, not just enums
Grammar: where Expression in List, where List is an X++ container, not a Set/List/Map/sub-query. Works with str, int, int64, real, enum, boolean, date, utcDateTime, RecId. One in clause per where; AND multiple set filters together.
container postingTypes = [LedgerPostingType::PurchStdProfit, LedgerPostingType::PurchStdLoss];
container accounts = ['1000', '2000', '3000'];
select sum(CostAmountAdjustment) from inventSettlement
where inventSettlement.OperationsPosting in postingTypes
&& inventSettlement.LedgerAccount in accounts;
❌ Never expand in into OR == OR == chains.
Other Learn-confirmed rules
- Field list before table when you don't need the full row —
select FieldA, FieldB from myTable where …. Never select * from.
firstOnly when at most one row is expected. Cannot be combined with next.forUpdate required before any .update() / .delete(); pair with ttsbegin / ttscommit.exists join / notExists join instead of nested while select for filter-only joins.- Outer join — only LEFT outer; no RIGHT outer, no
left keyword. Default values fill non-matching rows; distinguish "no match" vs "real zero" by checking the joined buffer's RecId.
- Join criteria use
where, not on. X++ has no on keyword.
index hint requires myTable.allowIndexHint(true) before the select; otherwise silently ignored. Only when measured.- Aggregates (
sum, avg, count, minof, maxof):
sum / avg / count work only on integer/real fields.- When
sum would return null (no rows), X++ returns NO row — guard with if (buffer) after.
- Non-aggregated fields in the select list must be in
group by.
forceLiterals is forbidden — SQL injection. Use forcePlaceholders (default for non-join selects) or omit.validTimeState(dateFrom, dateTo) for date-effective tables (ValidTimeStateFieldType ≠ None). Don't query without it unless you specifically want all historical rows.- Set-based ops (
RecordInsertList, insert_recordset, update_recordset, delete_from) over row-by-row loops for performance. They fall back to row-by-row only when an overridden table method, DB log, or alerts subscription forces it.
- SQL injection mitigation —
executeQueryWithParameters for dynamic queries; never concatenate strings into where.
- Timeouts — interactive 30 min, batch/services/OData 3 h. Override via
queryTimeout. Catch Exception::Timeout.
SysDa Framework — fluent query API
SysDa is the modern X++ query API — fluent and object-oriented. Use it when query shape depends on runtime conditions or when building reusable framework logic.
Core classes:
SysDaQueryObject — root query builder; set table buffer via constructor.SysDaSearchStatement — execute + iterate; SysDaFindStatement — firstOnly equivalent.SysDaUpdateStatement / SysDaInsertStatement / SysDaDeleteStatement — set-based ops.
CustTable custTable;
var qe = new SysDaQueryObject(custTable);
qe.whereClause(new SysDaEqualsExpression(
new SysDaFieldExpression(custTable, fieldStr(CustTable, AccountNum)),
new SysDaValueExpression('US-001')));
var so = new SysDaSearchStatement();
while (so.nextRecord(qe))
{
info(custTable.AccountNum);
}
Joins: qe.joinClause(SysDaJoinKind::InnerJoin, joinQe) — supports InnerJoin, OuterJoin, ExistsJoin, NotExistsJoin.
SysDa vs select — decision:
| Situation | Preferred |
|---|
| Static, compile-time query | select / while select — cleaner, compile-time field validation |
| Query shape depends on runtime conditions | SysDa |
| Building reusable framework / query logic | SysDa |
| Dynamically selecting fields or aggregates | SysDa |
Query Object Model — Query / QueryRun
Use Query / QueryRun when forms/reports bind to a shared query or the user can modify filters dynamically (e.g. SysQueryForm).
Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(CustTable));
qbds.addRange(fieldNum(CustTable, CustGroup)).value(queryValue('10'));
qbds.addSortField(fieldNum(CustTable, AccountNum));
QueryRun qr = new QueryRun(query);
while (qr.next())
{
CustTable ct = qr.get(tableNum(CustTable));
info(ct.AccountNum);
}
Key APIs:
SysQuery::findOrCreateRange(qbds, fieldNum) — idempotent range addition.QueryBuildDataSource::addDataSource() — nested join (child data source).qbds.joinMode(JoinMode::ExistsJoin) — set join type at runtime.query.allowCrossCompany(true) + query.addCompanyRange('dat') — cross-company at Query level.
Hard "never" list
- Never call functions in
where (e.g. where strFmt(...) == 'X') — assign to a local first; the optimizer can't index function expressions.
- Never use
today() (BP BPUpgradeCodeToday) — use DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()).
- Never nest
while select loops (BP BPCheckNestedLoopinCode) — joins, exists join, or pre-load to Map / temp table.
- Never call
doInsert / doUpdate / doDelete for normal business logic — they bypass overridden methods, framework validation, and event handlers. Reserved for data-fix / migration scripts only.
Pre-flight commands
d365fo get table <Table> --output json
d365fo find relations <Table> --output json
d365fo find usages <field|method> --output json
