condition based sum in select statement X++

Finance and Operations/AX Developer Forum
1

Hello All,

I want to write X++ select statement for getting sum based on condition; below is my SQL select statement.

select

SUM(Case when XQty =0 then YQty else XQty end) from XXXtable

Please suggest if this is achievable thru X++ sum function.

Thanks!

2

Hi Falguni,

if you are checking the condition on both quantities and returning the values based on that, try something as follows…

select sum(xqty),sum(yqty) from xxtable;

{

if(xqty == 0.00)

{

return yqty;

}

else

{

return xqty;

}

}

note: It is just pseudo code i have not tested in x++ code editor.

Regards,

Ven

3

You can’t do exactly the same, but you can easily get the same result by two queries:

select sum(XQty) from t1;
select sum(YQty) from t2
    where t2.XQty == 0;

t1.XQty + t2.YQty
4

Thanks for getting back, I suppose there is not any direct way to achieve this. Probably I ned to loop thru or trick with Martin’s answer.