Hi,

I would like to know If there is a possibility to do something like

alter table auftrag add sumfield computed by (sum(menge) from aufpos where
aufpos.auftragnr = auftrag.auftragnr)

Thanks for any help

Andreas

news:9p436q$k3c$01$1@news.t-online.com...

No. Aggregate functions like SUM cannot be part of a computed field. The
toll on performance would be very painful.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill

"Wayne Niddery [TeamB]" <wnidd...@aci.on.ca> wrote in message
news:3bb64792$1_1@dnews...

Wayne,

I have the following computed field working perfectly well

INVOICETOTAL computed by (cast((select sum(charge) from treatments where
treatments.invoicenumber = invoice.invoicenumber) as double precision))

Can I expect this to fall down around my ears at some point in the future
(Working perfectly well on over 100 sites ranging from 486's with Win95
upwards)?

Regards

Richard

news:3bb72d16_1@dnews...

I guess it really depends on how it is used. Obviously it *can* be done, but
I would want to be very careful and would tend to do this as part of a view
or proc rather than as a table definition.

Of course prudent writing of select statements would only include this
computed field if it were actually needed (versus "select * ..."), and if
its needed then this might in fact be more efficient then other methods. So
I'm open to persuasion here. <g>

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill

"Wayne Niddery [TeamB]" <wnidd...@aci.on.ca> wrote in message
news:3bb73d21_2@dnews...
<

I'll try <g>

In our database we store all invoices created by our system. This runs into
tens of thousands but our select statement only fetches those for the
specific patient when viewing their details (maybe a dozen or so invoices).
It seemed natural to have all the info from the table purely because any
table alterations would be in the one place without us forgetting there was
other views or procs requiring altering. It's just a small point but it does
lessen the metadata miss match that plagues us every so often (when users
decide not to install a vital upgrade and assume that jumping three or four
releases is perfectly okay <g>) The speed of using the computed field wasn't
much slower than without it (hardly noticeable) so all in all it seems to
have been the best way for us to go - but I'm open to persuasion too <g>

Richard

news:3bb8c4e1$1_2@dnews...

Hmmm, I'm not sure why you would have a metadata mismatch. Normally a
sum-type field is not stored in a field at all but is queried when needed.
The difference here is you will execute a single query on a patient for
these (e.g.) 12 invoices, but underneath, the computed field will cause 12
identical queries - each one computing the total of those 12 invoices.

I would drop the computed field and simply execute a second query that
returned the sum of those 12 records.

If I was really bothered by having to execute a 2nd query from the
application, I would create a stored proc that would act like your computed
field in that it returns the sum on every row returned, but in fact only
computes the sum once inside that proc.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill

"Wayne Niddery [TeamB]" <wnidd...@aci.on.ca> wrote in message
news:3bb8ea5d$1_2@dnews...

Hello Wayne,

The query fetches , say, 12 invoices but these 12 invoices will probably
have different totals. I'd go the second query route if it was just a
"grand" total required but I need each individual invoice total. The total
summing is done at the client end in a DevExpress Quantum Grid. Using a 3
invoice example the result set may look like

account    invoice    date                invoicetotal <--- computed field
104            1023    01/01/2001        46.50
104            1167    02/07/2001        78.90
104            2023    09/09/2001        12.45

These would then be summed at the client end by the grid.

It's not the sum of the 12 records I require. Each one of the invoices
references multiple treatments which more than likely means the invoice
totals will be different for each invoice.

Wouldn't the stored proc have to do exactly the same as the computed field
i.e. fetch all the treatment charges belonging to invoice one and total
them, then do the same for invoice two etc etc..?

It just seems that having the select query that is already being used to
fetch the rest of the table info was the best place to have the individual
invoice amounts calculated and returned. This results in one query from the
client, 12 records back from the server all calculated correctly and
available at the client end in one IBQuery. In practice it works well and is
very quick. Even doing a result set of 1300 invoices, all cross linked with
patient info, health company info, paid status etc etc (16 fields from four
joined tables) takes less than 1 1/2 seconds from request to display.

But I'm always looking for improvements and I appreciate your input <s>

Regards

Richard

news:3bba46e3$1_1@dnews...

Then I would use a group by:

select i.flda, i.fldb, ...i.fldn, sum(p.amount)
from invoices i
join payments p on p.invoiceno=i.invoiceno
group by i.flda, i.fldb, ...i.fldn

It would also use a group by.

Me too. When I get a chance I'll experiment more with such computed fields.
If I find it faster you can bet I'll use it, but I suspect each individual
case will need testing - as with many other things, such techniques don't
work in every case.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill

"Wayne Niddery [TeamB]" <wnidd...@aci.on.ca> wrote in message
news:3bba6616_2@dnews...

Thanks Wayne,

I'll try that to see how it performs in our app. Thanks for your suggestions

Richard

Hello Wayne,

Doing a quick test on using the group by query or the computed field seems
to make the group by slower than the computed field.
I did

select account, invoice, dat_, invoicetotal (computed field) from invoice

1300 records returned via IBconsole in 00:00:00:0040
(prepare time 00:00:00:0020)

the I did

select account, invoice, dat_, sum(treatments.charge) from
invoice
join treatments on treatments.invoice = invoice.invoice
group by account, invoice, dat_

1300 records returned in 00:00:00:220
(prepare time 00:00:00:0000)

I'm sure with suitable plans this could be tuned somewhat but the initial
results are interesting... I'll do some more indepth testing on it.

Regards
Richard