I need to calculate a SUM(tdsls045.copr*tdsls045.dqua):coste

When I compile the program I get the error:
ERROR SQL: Syntaz error: '*' not expected


It is not possible to do a product with a agregate function in the select?


Thanks,

María

Hi Maria,
You need to select the fields in the select statement, whose product you have to calculate.
In your case, the code should be something like this:

select tdsls045.copr, tdsls045.dqua, SUM(tdsls045.copr*tdsls045.dqua):coste

According to me, this should work.

Regards,
Amit

Hi amitmmokashi,

I selected the fields but the error is the same...
And I can´t select the fields because I need to do a SUM and I can´t do a GROUP BY with the fields tdsls045.copr and tdsls045.dqua ¿?

Next code fails:

declaration:
table ttdsls045
domain tcamnt coste
form.1:
init.form:
get.screen.defaults()
choice.cont.process:
on.choice:
select tdsls045.copr, tdsls045.dqua , sum(tdsls045.copr * tdsls045.dqua)
from tdsls045
where tdsls045._index1 = {500280,10,0}
selectdo
message("CANTIDAD: " & str$(tdsls045.dqua) &
" ; COSTE: " & str$(tdsls045.copr) &
" ; COSTE TOTAL: " & str$(tdsls045.copr*tdsls045.dqua))
endselect

Hi

This may help!!!!!!!!!

select tdsls045.copr, tdsls045.dqua
from tdsls045
where tdsls045._index1 = {500280,10,0}
selectdo
coste = coste + tdsls045.copr * tdsls045.dqua
message("CANTIDAD: " & str$(tdsls045.dqua) &
" ; COSTE: " & str$(tdsls045.copr) &
" ; COSTE TOTAL: " & str$(tdsls045.copr*tdsls045.dqua))
endselect

This is a solution, but I want to use the GROUP BY clause...
I would like to know if Baan allow a product in a agregate function (like SUM()) or this operation is not possible in the SELECT ¿?

This is not possible Baan SQLs though such a thing however is possible direct db queries .

If u specify the problem scenario , there could be some more relevent help on this .

I need to execute a query like this one:


select tdsls045.orno, tdsls045.pono,sum(tdsls045.copr*tdsls045.dqua):cost
from tdsls045
group by tdsls045.orno, tdsls045.pono


Thanks

I need to execute a query like this one:


select tdsls045.orno, tdsls045.pono,sum(tdsls045.copr*tdsls045.dqua):cost
from tdsls045
group by tdsls045.orno, tdsls045.pono


Thanks

I have always broke it down into two queries.

select tdsls045.orno, tdsls045.pono
from tdsls045
group by tdsls045.orno, tdsls045.pono
selectdo
total = 0
select a.copr:cost, a.dqua:qty
from tdsls045 a
where a._index1 = {:tdsls045.orno, :tdsls045.pono}
selectdo
total = total + cost*qty
endselect
| Now do some other stuff
endselect

I have never found a better way. Of course this was only for when I needed a total like this in the code. If I was doing a report I would use one query and then let the report do the total.

1. product totals in sql query as idnicated in example is not available in B40.

2. In a non-repeating simples scenario like the one orno-pono simple case , can be done by accumulating variables within selectdo , rprt_sends (if printing) and appropriate re-initialization .

3. If need is to have totals in report along with details lines intervening between 2 consecutive sortfield totals , then totalling through reports is the best way .

4. If how one needs totals in report without detail lines OR one needs totals at end or beginning of the report separate from detail lines , then memory based array totalling is the best and fastest way to do so. We have umpteen examples at our end to with excellent results at our end . U may check performance dll (http://www.baanboard.com/baanboard/showthread.php?t=28569) and migrating disk based totals in script to memory based (http://www.erpjewels.com/Code%20Generator%20for%20data%20%20transformation.htm) for mor information .