March 30, 2004 at 4:09 pm
Hello
I need to make an update statment that calculate for each invoice the sum of field (fi.peso*fi.qtt) is weight*quantity, i write the following update :
update ft set ft.peso=(select sum(fii.peso*qtt)from fi fii inner join ft on fii.ftstamp=ft.ftstamp group by ft.fno)
from ft inner join fi fii on ft.ftstamp=fii.ftstamp and ft.fdata between '20040201' and '20040229' group by ft.fno
the field ft.ftstamp is my index of heading invoices and that was the same for each lines with the same nº of invoice
the table ft(heading of the invoices) is the table that contains the totals of my table fi (lines of invoices)
if i run only the subselect they returned for each invoice the totals of (fi.peso*fi.qtt):
76.740000
8062.752000
297.504000
148.680000
18.744000
27.000000
36.347000
325.440000
234.929000
.000000
18.000000
100.000000
62.004000
152.256000
20.484000
116.840000
but when i run all the TSQL statment they return me an error :
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'group'.
how i can build this TSQL statment to run correctly for recalculate the totals of each invoice headings
Many thanks
Luis Santos
March 30, 2004 at 10:59 pm
your last "group by" is not allowed.
How about :
update ft
set ft.peso= fiii.NewPeso
from ft
inner join fi fii
on ft.ftstamp=fii.ftstamp
and ft.fdata between '20040201' and '20040229'
inner join
(select ft.fno as fno , sum(fii.peso*qtt) as NewPeso
from fi fii
inner join ft
on fii.ftstamp=ft.ftstamp
group by ft.fno) fiii
on ft.fno = fiii.fno
OR depending on semantics
update ft
set ft.peso= fiii.NewPeso
from ft
inner join
(select ft.fno as fno , sum(fii.peso*qtt) as NewPeso
from fi fii
inner join ft
on fii.ftstamp=ft.ftstamp
and ft.fdata between '20040201' and '20040229'
group by ft.fno) fiii
on ft.fno = fiii.fno
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply