D365 query

---

简单查询

Query  query;
QueryBuildDataSource    qbds;
QueryBuildRange qbr,
QueryRun                queryRun;query = new Query(); //新建查询对象
qbds = query.addDataSource(tableNum(SalesTable))
qbr= qbds.addRange(fieldNum(SalesTable, salesId));
qbr.value(queryValue(salesId));queryRun = new queryRun(query);
queryRun.allowCrossCompany(true);if (queryRun .prompt())
{while (queryRun .next()){SalesTable salesTable= queryRun.Get (tableNum(SalesTable)); // 从queryrun中获取记录info(salesTable.salesId);}

---

多个表联接

一

A
|-B （1：n， innerJoin）
|–C （1：n， innerJoin）

Query q = new query();
QueryRun qr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbds2;
QueryBuildDataSource qbds3;
QueryBuildRange qbr;qbds = q.addDataSource(tableNum(A));qbds3 = qbds.addDataSource(tableNum(B));
qbds3.relations(false);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.addLink(fieldnum(A, RecId),fieldnum(B, ARecId));qbds2 = qbds.addDataSource(tableNum(C));
qbds2.relations(false);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.addLink(fieldnum(B, RecId),fieldnum(C, BRecId));qr = new QueryRun(q);while (qr.next()){//Insert code here}

等价于

Select AJoin B where B.ARecId == A.RecIdJoin C where C.BRecId == B.RecId;

二

A
|-B （1：n， innerJoin）
|-C （1：n， innerJoin）

Query q = new query();
QueryRun qr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbds2;
QueryBuildDataSource qbds3;
QueryBuildRange qbr;qbds = q.addDataSource(tableNum(A));qbds3 = qbds.addDataSource(tableNum(B));
qbds3.relations(false);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.addLink(fieldnum(A, RecId),fieldnum(B, ARecId));qbds2 = qbds.addDataSource(tableNum(C));
qbds2.relations(false);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.addLink(fieldnum(A, RecId),fieldnum(C, BRecId));qr = new QueryRun(q);while (qr.next()){//Insert code here}

等价于

Select A Join B where B.ARecId == A.RecId;
Select A Join C where C.BRecId == A.RecId;

二

A
|-B （1：n， innerJoin）
|-C （1：n， innerJoin）
两个子表是在同一个level的

Query q = new query();
QueryRun qr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbds2;
QueryBuildDataSource qbds3;
QueryBuildRange qbr;qbds = q.addDataSource(tableNum(A));qbds3 = qbds.addDataSource(tableNum(B));
qbds3.relations(false);
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.addLink(fieldnum(A, RecId),fieldnum(B, ARecId));qbds2 = qbds.addDataSource(tableNum(C));
qbds2.relations(false);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.addLink(fieldnum(A, RecId),fieldnum(C, BRecId));qr = new QueryRun(q);while (qr.next()){//Insert code here}

等价于

先查询：Select A;
再查询：Select A Join B where B.ARecId == A.RecId;
最后：Select A Join C where C.BRecId == A.RecId;

三

A
|-B （1：1， innerJoin）
|-C （1：1， innerJoin）
两个子表是在同一个level的

Query q = new query();
QueryRun qr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbds2;
QueryBuildDataSource qbds3;
QueryBuildRange qbr;qbds = q.addDataSource(tableNum(A));qbds3 = qbds.addDataSource(tableNum(B));
qbds3.relations(false);
qbds2.fetchmode(QueryFetchMode::One2One);//fetchmode
qbds3.joinMode(JoinMode::InnerJoin);
qbds3.addLink(fieldnum(A, RecId),fieldnum(B, ARecId));qbds2 = qbds.addDataSource(tableNum(C));
qbds2.relations(false);
qbds2.fetchmode(QueryFetchMode::One2One);//fetchmode
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.addLink(fieldnum(A, RecId),fieldnum(C, BRecId));qr = new QueryRun(q);while (qr.next()){//Insert code here}

等价于

Select AJoin B where B.ARecId == A.RecIdJoin C where C.BRecId == A.RecId;

将FetchMode设置为 “1:1”时，才可以得到这样的查询语句