Simple X++ query WHERE clause

Finance and Operations/AX Developer Forum
1

Hi,

I am an X++ beginning programmer. Can somebody please help me to get my query to run with either of the 2 where clauses(separately of course)? I want to run the query once to find records with positive amounts and then I want to run it again to find a separate result set of transactions with dates after 1/1/2014.

static void GroupByVendTransWhere(Args _args)

{

VendTrans vendTrans;

;

while select sum(AmountMST) from vendTrans
group by AccountNum
// where sum(AmountMST) > 0
// where TransDate > 01\01\2014

print vendTrans.AccountNum, " ",vendTrans.AmountMST;

pause;
}

2

HI ,

Please try the below code.

static void GroupByVendTransWhere(Args _args)

{

VendTrans vendTrans;

transdate _date;

;

_date = mkdate(01,01,2014);

select sum(AmountMST) , AccountNum from vendTrans group by AccountNum

where vendTrans.AmountMST > 0

&& vendTrans. TransDate > _date ;

print vendTrans.AccountNum, " ",vendTrans.AmountMST;

pause;
}

3

If you want to filter by the result of an aggregate function (such as sum()), you have to use HAVING. It’s supported by the Query Framework, not by queries directly in X++. See an example in Walkthrough: Creating an AOT Query that has Group By and Having Nodes.

The support for HAVING was introduced in AX 2012. Unfortunately you didn’t mention your version of AX…

4

Thank you Abdul for correcting my query. Why does my date criteria require a variable be created? I tried commenting out the _date variable and just using the date syntax in the && line of the code, but it wouldn’t run.

@ Martin…my AX is 2012 R1. Thanks!

5

I hope you’re aware of the difference between your original pseudo-code (where sum(AmountMST) > 0) and the current implementation (where AmountMST > 0). The former one would group all transactions and filter out groups with non-positive summary, while the latter is a sum of positive transactions only; negative transactions are completely ignored.

What error did you get with TransDate > mkDate(1, 1, 2014)? It should work.

6

Got it Martin. I wasn’t aware that mkdate was a function. When I added it the && line in my code it ran fine. Thanks! Yes, I’m aware my code needs to have some kind of X++ “HAVING” code after the “GROUP BY” so I can get the sum of transactions by vendor from VENDTRANS. Not a very practical report dataset, but something I can use to practice proper code syntax. Assuming I can ever find a good X++ GroupBy/Having example.