I have a scenario whereby I have "one to many" link. I need to sum up a couple of value fields from the "many" table.

I know this can be done within Baan SQL, using the Group by function. Just dont know the detailed script syntax. I am also not sure where the "sum" value would be stored.

Any assistance would be appreciated.

Please see below code, may be helpful to you.

select tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv, tisfc001.osta,
ticst001.sitm, sum(ticst001.qucs):est.qty
from tisfc001, ticst001
where tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
{:cprj.t,:item.t,:pdno.t}
and tisfc001.osta BETWEEN 5 and 7
and ticst001._index3 inrange {:rm.item.f} and {:rm.item.t}
and ticst001.pdno = tisfc001.pdno
group by tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv,
tisfc001.osta, ticst001.sitm

I have a scenario whereby I have "one to many" link. I need to sum up a couple of value fields from the "many" table.

I know this can be done within Baan SQL, using the Group by function. Just dont know the detailed script syntax. I am also not sure where the "sum" value would be stored.

Tried using your example, but have "invalid token" on compile.
Seems like a problem with totaling a array element

the sql.....

function get.material.data()
{
select tssoc220.orno, tssoc220.acln, sum(tssoc220.acco(2)):mats.cost
from tssoc220
where tssoc220.orno = tssoc210.orno and
tssoc220.acln = tssoc210.acln
group by tssoc220.orno, tssoc220.acln
selectdo
endselect
}


Any one out there able to assist any further ?

}

Hi,

Here is the Group By manual link (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_group_by)

I hope it helps,

- George

function get.material.data()
{
select tssoc220.orno, tssoc220.acln, sum(tssoc220.acco(2)):mats.cost
from tssoc220
where tssoc220.orno = tssoc210.orno and
tssoc220.acln = tssoc210.acln
group by tssoc220.orno, tssoc220.acln
selectdo
endselect
}

when use tssoc210.acln this table is not in the from clause and the where clause might be tssoc220.acln = :tssoc210.acln

Correct your SQL in from clause as below

function get.material.data()
{
select tssoc220.orno, tssoc220.acln, sum(tssoc220.acco(2)):mats.cost
from tssoc220, tssoc210
where tssoc220.orno = tssoc210.orno
and tssoc220.acln = tssoc210.acln
group by tssoc220.orno, tssoc220.acln
selectdo
endselect
}

function get.material.data()
{
select tssoc220.orno, tssoc220.acln, sum(tssoc220.acco(2)):mats.cost
from tssoc220
where tssoc220.orno = tssoc210.orno and
tssoc220.acln = tssoc210.acln
group by tssoc220.orno, tssoc220.acln
selectdo
endselect
}