[转]Microsoft dynamics Ax Query 实现 union 关键字

Ax中多表关联仅提供Inner Join/Outer Join/Exist join/Not Exist join,而么有union,可以通过Query来实现.

例如 Sql:

select ItemType,GoodsName from table1 where TransDate between g_DateB and g_DateE
union
select ItemType,GoodsName from table2 where TransDate between g_DateB and g_DateE

1 实现[ from table1 ]
qbd = query.addDataSource(tableNum(table1));
2 实现[ select ItemType,GoodsName ]
qbd.addSelectionField(fieldnum(table1,ItemType));
qbd.addSelectionField(fieldnum(table1,GoodsName));
3 实现[ where TransDate between g_DateB and g_DateE ]
qbd.addRange(fieldnum(table1,TransDate)).value(strfmt(“%1..%2”, g_DateB,g_DateE));
4 实现[ union ]
query.queryType(QueryType::Union);

代码:

Query query;
QueryRun qr;
QueryBuildDataSource qbd;
QueryBuildDataSource qbd2;
Table1 newTable;

qbd = query.addDataSource(tableNum(table1));
qbd.addSelectionField(fieldnum(table1,ItemType));
qbd.addSelectionField(fieldnum(table1,GoodsName));
qbd.addRange(fieldnum(table1,TransDate)).value(strfmt("%1..%2", g_DateB,g_DateE));

qbd2 = query.addDataSource(tablenum(table2));
qbd2.addSelectionField(fieldnum(table2,ItemType));
qbd2.addSelectionField(fieldnum(table2,GoodsName));
qbd2.addRange(fieldnum(table2,TransDate)).value(strfmt("%1..%2", g_DateB,g_DateE));

qbd2.addRange(fieldnum(table2,RecId)).
       value(strfmt("((%1 > %2) || (%3 > %4) || (%5 > %6))",
       fieldstr(table2,Qty),queryValue(0),
       fieldstr(table2,Weight),queryValue(0),
            fieldstr(table2,Volume),queryValue(0)));// OR Condition

query.queryType(QueryType::Union);
qr = new QueryRun(query);
while(qr.next())
{
     newTable = qr.get(tableNum(table1));
     //do something...
}

原文地址：http://www.cnblogs.com/eugenewu0808/archive/2011/08/31/2160914.html

MSDN文章：How to: Combine Data Sources in a Union Query [AX 2012]

转载请注明：ww12345678 的部落格 | AX Helper » [转]Microsoft dynamics Ax Query 实现 union 关键字