Ax Queries by using X++

Hi Guys

I am having a problem in writing an ax query to find the customers who are having more than 10 sales orders.

Thanks in advance…

Do u need a job or individual customer related SO?

Hi Matize

I need a job for testing that query.

Hi Matize

I need a job for testing that query.

Hi Harish,

Find the below code which will help you ;

static void SalesOrderMoreThenTen(Args _args)

{

SalesTable salestable, st;

int i =0;

;

while select count(recid) from salestable

group by salestable.CustAccount

{

if(int642int(salestable.RecId) > 10)

{

info(salestable.CustAccount);

}

}

}

If you use AX2012, you can write it in a very efficient way by using HAVING clause. Unfortunately it can’t be used directly in select statements in X++, but it is supported by the Query framework. See the example:

Query query = new Query();
QueryBuildDataSource salesDs = query.addDataSource(tableNum(SalesTable));
QueryRun qr;
SalesTable st;
;

salesDs.addSelectionField(fieldNum(SalesTable, RecId), SelectionField::Count);
salesDs.addGroupByField(fieldNum(SalesTable, CustAccount));
query.addHavingFilter(salesDs, fieldStr(SalesTable, RecId), AggregateFunction::Count).value('>10');

qr = new QueryRun(query);
while (qr.next())
{
	st = qr.get(tableNum(SalesTable));
	info(strFmt("%1 %2", st.CustAccount, st.RecId));
}

The resulting query is:

SELECT COUNT(RecId) FROM SalesTable(SalesTable_1) GROUP BY SalesTable.CustAccount HAVING ((COUNT(RecId)>10))

Thank you vishal and Martin for your support…Martin I am using AX 2009…

Pls verify it, once done…it will help others to find the reolved solution.

static void HARCustmorMoreThanTenSO(Args _args)
{
Query query;
QuerybuildDataSource qbdSales;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
SalesTable salesTable;
;

query = new Query();
qbdSales = query.addDataSource(tablenum(salesTable));
qbdSales.addSelectionField(fieldnum(salesTable,RecId),SelectionField::Count);
qbdSales.addGroupByField(fieldnum(salesTable,CustAccount));

queryRun = new QueryRun(query);

while(queryRun.next())
{
salesTable = queryRun.get(tablenum(SalesTable));
if(salesTable.RecId > 10)
{
info(salesTable.CustAccount);
}
}
}

I got the solution by using the following query

You just have to realize that this query fetches data for all sales orders and process them in AX. If you can use HAVING, all the work is done by database server, which is much faster.

True martin!

Martin, Is HAVING available in AX 2009?

Unfortunately no - that’s why I wrote “If you use AX2012, you can write it in a very efficient way by using HAVING clause”. Support for HAVING was introduced in AX2012, it doesn’t exist in older versions.

Hi,

I am facing the same issue in AX 2009… is there any way to achieve like "HAVING clause" in Ax 2009 …