Subject | Re: [firebird-support] View limitations |
---|---|
Author | Alexandre Benson Smith |
Post date | 2007-08-14T20:10:56Z |
Hi Dmitry !
Dmitry Yemanov wrote:
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 :-)
I could also implement the scanning/multiply loop at client side, but I
don't like it since I would need to replicate it over some places
(client app, reports using crystal reports and so on)
I will try to expose the problem and maybe someone shed some light.
I have a case where the value of a product should be valued over the
time using a daily factor based on the inflation. The factor is the
variance from the day before to the current day (so it's not
cumulative), I would have values like this:
Date Factor
2007-01-01 1.0
2007-01-02 1.1
2007-01-03 1.2
2007-01-04 1.1
2007-01-05 1.15
2007-01-06 1.3
2007-01-07 0.98
2007-01-08 1.2
2007-01-09 0.8
2007-01-10 1.3
(Of course the values are just samples...)
Let's suppose I bought a product by USD 100 on day 1 and I need to know
how much is the actual value when I was on day 3, the result would be:
100 * (1.1 * 1.2) = 100 * 1.32 = USD 132
Then I have the same product bought on day 2 by USD 100 too, and I need
to know much is the actual value when I was on day 3, the result would be:
100 * (1.2) = USD 120
Then I bought again the same product by USD 100 on day 3 and I need to
know the actual value on day 7:
100 * (1.1 * 1.15 * 1.3 * 0.98) = 100 * 1.61161 = USD 161.16
I wish a multiply aggregate function so I could do something on this line:
select
P.Cost as OriginalCost, P.PurchaseDate,
(select Multiply(F.Factor) from Factors F where F.Date between
P.PurchaseDate + 1 and CurrentDate) as AccumulatedFactor,
P.Cost * (select Multiply(F.Factor) from Factors F where F.Date
between P.PurchaseDate + 1 and CurrentDate)
from
Products P
I think the approach I would follow is to have a secondary column that
would hold the accumulated factor, something like this:
Date Factor Accumulated
2007-01-01 1.0 1.0
2007-01-02 1.1 1.1
2007-01-03 1.2 1.32
2007-01-04 1.1 1.452
2007-01-05 1.15 1.6698
2007-01-06 1.3 2.17074
2007-01-07 0.98 2.12732
2007-01-08 1.2 2.5528
2007-01-09 0.8 2.04223
2007-01-10 1.3 2.6549
After that I could issue a query like this:
select
P.Cost as OriginalCost, P.PurchaseDate,
(select F.Factor from Factors F where F.Date = CurrentDate) / (select
F.Factor from Factors F where F.Date = P.PurchaseDate + 1) as
AccumulatedFactor,
P.Cost * (select F.Factor from Factors F where F.Date = CurrentDate)
/ (select F.Factor from Factors F where F.Date = P.PurchaseDate + 1)
from
Products P
I don't like that much this approach since I would need to put logic to
handle a total recalculation of the cumulative values if for some reason
one discovers that the value used on a day three months ago was typed
wrong (not hard to do, but I avoid persist things that could be
generated on the fly).
I hope I could explain my needs.
Any suggestions ?
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br
Dmitry Yemanov wrote:
> PenWin wrote:I post a message on fb-architect about an aggregate function that
>
>> SELECT *
>> FROM sometable
>> LEFT JOIN someprocedure(sometable.key) ON 1=1
>>
>> I wonder if it is a known limitation of the engine or if it is a bug.
>>
>
> As the error message tells you "feature is not supported", I doubt it
> should be named a bug ;-)
>
> Views cannot be based on stored procedures, this is a known limitation.
>
>
> Dmitry
>
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 :-)
I could also implement the scanning/multiply loop at client side, but I
don't like it since I would need to replicate it over some places
(client app, reports using crystal reports and so on)
I will try to expose the problem and maybe someone shed some light.
I have a case where the value of a product should be valued over the
time using a daily factor based on the inflation. The factor is the
variance from the day before to the current day (so it's not
cumulative), I would have values like this:
Date Factor
2007-01-01 1.0
2007-01-02 1.1
2007-01-03 1.2
2007-01-04 1.1
2007-01-05 1.15
2007-01-06 1.3
2007-01-07 0.98
2007-01-08 1.2
2007-01-09 0.8
2007-01-10 1.3
(Of course the values are just samples...)
Let's suppose I bought a product by USD 100 on day 1 and I need to know
how much is the actual value when I was on day 3, the result would be:
100 * (1.1 * 1.2) = 100 * 1.32 = USD 132
Then I have the same product bought on day 2 by USD 100 too, and I need
to know much is the actual value when I was on day 3, the result would be:
100 * (1.2) = USD 120
Then I bought again the same product by USD 100 on day 3 and I need to
know the actual value on day 7:
100 * (1.1 * 1.15 * 1.3 * 0.98) = 100 * 1.61161 = USD 161.16
I wish a multiply aggregate function so I could do something on this line:
select
P.Cost as OriginalCost, P.PurchaseDate,
(select Multiply(F.Factor) from Factors F where F.Date between
P.PurchaseDate + 1 and CurrentDate) as AccumulatedFactor,
P.Cost * (select Multiply(F.Factor) from Factors F where F.Date
between P.PurchaseDate + 1 and CurrentDate)
from
Products P
I think the approach I would follow is to have a secondary column that
would hold the accumulated factor, something like this:
Date Factor Accumulated
2007-01-01 1.0 1.0
2007-01-02 1.1 1.1
2007-01-03 1.2 1.32
2007-01-04 1.1 1.452
2007-01-05 1.15 1.6698
2007-01-06 1.3 2.17074
2007-01-07 0.98 2.12732
2007-01-08 1.2 2.5528
2007-01-09 0.8 2.04223
2007-01-10 1.3 2.6549
After that I could issue a query like this:
select
P.Cost as OriginalCost, P.PurchaseDate,
(select F.Factor from Factors F where F.Date = CurrentDate) / (select
F.Factor from Factors F where F.Date = P.PurchaseDate + 1) as
AccumulatedFactor,
P.Cost * (select F.Factor from Factors F where F.Date = CurrentDate)
/ (select F.Factor from Factors F where F.Date = P.PurchaseDate + 1)
from
Products P
I don't like that much this approach since I would need to put logic to
handle a total recalculation of the cumulative values if for some reason
one discovers that the value used on a day three months ago was typed
wrong (not hard to do, but I avoid persist things that could be
generated on the fly).
I hope I could explain my needs.
Any suggestions ?
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br