Subject Re: [firebird-support] View limitations
Author jft
Alexandre,
As Ivan has sugggested, logarithms are the way to go - an additional column on your factors table (updated by triggers or as a calculated column) for the log of each day's factor; sum the logs for the relevant days and calculate the antilog of that.
The functions you will need are in the standard FB issue - log (ib_udf) and dpower(fbudf).
Using your example figures the calculations beccome:
SQL> select (1.1*1.15*1.3*0.98) Product from RDB$DATABASE;
PRODUCT
=====================
1.611610
SQL> select dpower(10,sum(LogFact)) CumulativeProduct from (
CON> select 1.10 Factor, log(10,1.10) LogFact from RDB$DATABASE
CON> union select 1.15, log(10,1.15) from RDB$DATABASE
CON> union select 1.30, log(10,1.30) from RDB$DATABASE
CON> union select 0.98, log(10,0.98) from RDB$DATABASE) as DF;
CUMULATIVEPRODUCT
=======================
1.611610000000000
Cheers,
John
> -------Original Message-------
> From: Ivan Prenosil <Ivan.Prenosil@...>
> Subject: Re: [firebird-support] View limitations
> Sent: 15 Aug '07 08:53
>
> Alexandre,
> > I post a message on fb-architect about an aggregate function that
> > multiplies the column values instead of summing it up.
> >
> > This request could be done using a selectable SP, but I need it working
> > inside views, as you know UDF's is not an option for aggregates, so I
> > have a problem :-)
>
> Perhaps you can add UDFs for logarithm and exponential functions and do this:
>
>   SELECT Exp( SUM( Ln(x) ) ) FROM ...
>
> Ivan
> http://www.volny.cz/iprenosil/interbase/
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>