Subject Re: [firebird-support] View limitations
Author Alexandre Benson Smith
Hi John!

jft wrote:
> 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;
> =====================
> 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;
> =======================
> 1.611610000000000
> Cheers,
> John

The solution Ivan provided fits like a glove, I will avoid the storage
of the log value on a secondary column and do all the calc on the fly
when needed, I think the performance for the log calc will not be big .

My Statement will end up like this:
Factors f
Data between '2007-01-04' and '2007-01-07'

Thanks !

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil