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 A
	Join B where B.ARecId == A.RecId
	Join 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 A
	Join B where B.ARecId == A.RecId
	Join C where C.BRecId == A.RecId;

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