Dynamics Community Forum Thread Details

rewriting T-SQl query with having clause to AX programmatic query

Hello,

I have rewritten this t-sql query

select ConnectionId, ContractNum, min(ENDDATETIME) from MCSCMBILAGREEMENT
where startdatetime <> enddatetime
group by ConnectionId, ContractNum
having count(ConnectionId) > 1
order by min(ENDDATETIME) desc

to AX programmatic query

static void Job10(Args _args)
{
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbdsMcsCmBilAgreement;
McsCmBilAgreement agreement, agreement2;

qbdsMcsCmBilAgreement = query.addDataSource(tableNum(McsCmBilAgreement));

qbdsMcsCmBilAgreement.addSortField(fieldNum(McsCmBilAgreement,StartDateTime),SortOrder::Ascending);

qbdsMcsCmBilAgreement.addGroupByField(fieldNum(McsCmBilAgreement, connectionid));
qbdsMcsCmBilAgreement.addGroupByField(fieldNum(McsCmBilAgreement, contractnum));

query.addHavingFilter(qbdsMcsCmBilAgreement, fieldStr(McsCmBilAgreement, connectionid), AggregateFunction::Count).value("> 1");

info(query.toString());
queryRun = new QueryRun(query);

while(queryRun.next())
{
agreement = queryRun.get(tableNum(McsCmBilAgreement));

info(strFmt("%1 %2 %3 %4", agreement.ConnectionId, agreement.ContractNum, agreement.StartDateTime, agreement.EndDateTime));

}
}

how to add this clause where startdatetime <> enddatetime to the AX query from above?

thanks.

Categories:

Microsoft Dynamics AX

Quick Links

Leaderboard