Good morning Baan guru's,

Does anyone know how to use the "min" function in a report script? One of my members of Production would like me to have BaaN reflect the next "operation" which has not yet been reported complete for a production order. I have managed to get this min function to work in "SQL Queries" but not able to get it to work in a report script.

Your input is greatly appreciated if you have used this function before in a report script. Thanking you in advance.

Hi chris_kzn,

can I see your report script query?

Best regards
michael

Hi Michael,

Attached are the different approaches I have attempted. I am just now wishing to get it into a newly created script.

text script as follows:

declaration:
table ttisfc010
table ttisfc001

extern domain tcopno nextop

detail.1:
before.layout:
select min(tisfc010.opno):nextop
from tisfc010,tisfc001
where tisfc010._index1 = {:tisfc001.pdno}
selectdo
endselect

Hi chris_kzn,

Looking at screen shot you have attached it seems that you are trying to use aggregate function in detail layout of report which is not as per the BaaN guidelines. To get desired result use aggregate function in 'footer', 'after.field' or 'after.report' layout only.


Considering your select query it should give proper result if you have maintained field 'tisfc001.pdno' as report input field. Also in this query you can remove tisfc001 as follows -

select min(tisfc010.opno):nextop
from tisfc010
where tisfc010._index1 = {:tisfc001.pdno}
selectdo
endselect

All The Best !!!

Last but not least I would not use min for that:


domain tcpdno dummy.pdno | just to avoid side effects
select tisfc010.pdno:dummy.pdno,
tisfc010.opno:nextop
from tisfc010
where tisfc010._index1 = {:tisfc001.pdno}
order by tisfc010._index1 asc
as set with 1 rows | -> implies min
selectdo
endselect


Günther

I just wanted to say thanks to all who have tried to assist.