Hi Dmitry !


Dmitry Yemanov wrote:

> PenWin wrote:
>
>> 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
>

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 :-)

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