I'm trying to sum all the tfcmg101.amnt field by using this method, it give me correct result.

select tfcmg101.ptbp, sum(tfcmg101.amnt):atotal
from tfcmg101
where tfcmg101.ptbp =:tfcmg101.ptbp and tfcmg101.btno = :tfcmg101.btno
and tfcmg101.cnum = :tfcmg101.cnum
and tfcmg101.ccur = :tfcmg101.ccur
group by tfcmg101.ptbp
selectdo
endselect

But, now i have condition for tfcmg101.amnt which is as below.

if (tfcmg101.tadv = tfcmg.tadv.slsinv) and (tfcmg101.amnt > 0) then
tfcmg101.amnt = tfcmg101.amnt * (-1)
endif

If it meet this condition then it should take the value for tfcmg101.amnt multiple with -1, before do the sum for tfcmg101.amnt.

Why the result is still same even i already put a condition? Any idea for this?

I think Both things are independent, SUM functions is applicable to the original set of data fetched by the query. I guess your are putting this condition on SELECTDO.

Where the condition put also give same result. For example:

tfgld101.amnt = 100, 120, 130

if sum then it will be 100+120+130 = 350

Base on the condition that i set, that 120 suppose to become -120

So it should be something like this, 100-120+130 = 110

But it will not give me this result if i write the script like i posted above. Anything wrong with the script?

if (tfcmg101.tadv = tfcmg.tadv.slsinv) and (tfcmg101.amnt > 0) then
tfcmg101.amnt = tfcmg101.amnt * (-1)
endif
Why the result is still same even i already put a condition? Any idea for this?

Difficult to tell you what is wrong as above code is correct. To conclude anything; we need to know whether you have written above code in proper place or not. Therefore, could you please post the exact code?

And also check whether proper test data is available to check this scenario.

To get the result you want you will have to do somewhat as follows:


atotal = 0
select tfcmg101.*
from tfcmg101
where tfcmg101.ptbp =:tfcmg101.ptbp and tfcmg101.btno = :tfcmg101.btno
and tfcmg101.cnum = :tfcmg101.cnum
and tfcmg101.ccur = :tfcmg101.ccur
order by tfcmg101.ptbp
selectdo
if (tfcmg101.tadv = tfcmg.tadv.slsinv) and (tfcmg101.amnt > 0)
then
atotal = atotal - tfcmg101.amnt
else
atotal = atotal + tfcmg101.amnt
endif
endselect



The select has to complete creation of the record set before the SELECTDO will execute. When you use the aggregate function, the select has already finished summing up the amnt field into atotal.
Also, not that it will matter when using aggregate functions, in your code you have not selected the field tfcmg101.tadv, so it has an EMPTY value and will always be false.