Given invoiceheader and invoicedetail tables, I need to update a field net_total in the invoiceheader table with the sum of the related detail rows (field
net_amount)

My SQL knowledge isn't sufficient for this (yet)...

Any help appreciated

TIA
Darrell

Darrell wrote:

> Given invoiceheader and invoicedetail tables, I need to update a
> field net_total in the invoiceheader table with the sum of the
> related detail rows (field net_amount)
>
> My SQL knowledge isn't sufficient for this (yet)...
>
> Any help appreciated
>
> TIA
> Darrell

Kia Orana Darrell

Something like the following:-

SELECT InvoiceNo, SUM(net_amount) AS Total
INTO MEMORY Temp
FROM invoiceheader
;
UPDATE invoiceheader h
SET net_total = t.Total
FROM invoiceheader h
LEFT OUTER JOIN MEMORY Temp t ON (t.InvoiceNo = h.InvoiceNo)
;
DROP TABLE MEMORY Temp


Note that I have invented a field InvoiceNo as the thing that ties the
two tables together.  Also the "MEMORY" syntax has changed since DBISAM
v3.30 which I'm still using.

Hope that helps

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

"Jeff Cook" wrote:

Darrell wrote:

> Given invoiceheader and invoicedetail tables, I need to update a
> field net_total in the invoiceheader table with the sum of the
> related detail rows (field net_amount)
>
> My SQL knowledge isn't sufficient for this (yet)...
>
> Any help appreciated
>
> TIA
> Darrell

Kia Orana Darrell

Something like the following:-

SELECT InvoiceNo, SUM(net_amount) AS Total
INTO MEMORY Temp
FROM invoiceheader
;
UPDATE invoiceheader h
SET net_total = t.Total
FROM invoiceheader h
LEFT OUTER JOIN MEMORY Temp t ON (t.InvoiceNo = h.InvoiceNo)
;
DROP TABLE MEMORY Temp


Note that I have invented a field InvoiceNo as the thing that ties the
two tables together.  Also the "MEMORY" syntax has changed since DBISAM
v3.30 which I'm still using.

Hope that helps

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com



Jeff ~

thanks for your reply -
I feel it should be noticeably simpler than your example - something along the lines of:


update invoiceheaders h
 set   h.nettotal =  ( select sum(d.netamount) from invoicedetails d where d.invoicenumber = h.invoicenumber )
where <condition>

which gives me a parse error at 'sum' that I cannot figure out

Darrell

Darrell wrote:

<<Jeff ~

thanks for your reply -
I feel it should be noticeably simpler than your example - something
along the lines of:


update invoiceheaders h
 set   h.nettotal =  ( select sum(d.netamount) from invoicedetails d
where d.invoicenumber = h.invoicenumber )
where <condition>

which gives me a parse error at 'sum' that I cannot figure out

Darrell>>

Don't know enough about v4 to say what is wrong - but I suspect that
aggregates aren't allowed in sub SELECTs.

As for being simpler - the only difference is that my example saves the
sub SELECT that you are doing already in a intermediate results table.

Mine is probably faster too as yours will have run a select for every
invoice whereas mine runs it just once.  That assumes that you are
updating multiple invoices though.



--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Darrell


I haven't been keeping up with V4 (let my subscription lapse after switching to ElevateDB) but I didn't know DBISAM supported subselects.

Roy Lambert

"Darrell" <dfunkmeister@gmail.com> wrote in message
news:DEB98A52-9B5A-4CB8-AF85-136686D83628@news.elevatesoft.com...
>
> Hope that helps
>
> thanks for your reply -
> I feel it should be noticeably simpler than your example - something along
> the lines of:
>
>
> update invoiceheaders h
>  set   h.nettotal =  ( select sum(d.netamount) from invoicedetails d where
> d.invoicenumber = h.invoicenumber )
> where <condition>
>
> which gives me a parse error at 'sum' that I cannot figure out
>

the subquery is, afaik, only available for selects.

if nettotal is zero to start with, you can update it adding every netdetail
individually.

update invoiceheaders set nettotal = nettotal + netamount
from invoiceheaders
join invoicedetails on invoicedetails.invoicenumber =
invoiceheaders.invoicenumber

if nettotal is null to start with, then test for null and if true,
if(nettotal = null then nettotal = netamount else nettotal = nettotal +
netamount)

but if what you want is a replacement of exisitng values, i think you need a
script as suggested by other poster.

Robert

Darrell,

<< I feel it should be noticeably simpler than your example - something
along the lines of: >>

DBISAM doesn't support using SELECT expressions as scalar values.  Our newer
product ElevateDB does.

--
Tim Young
Elevate Software
www.elevatesoft.com