Hello,

I have a table that contains the fields PERIOD, STOCK, NEW_UNITS,
CHURN_RATE. STOCK is only available for a given start period. I want
to update the expected stock of the next periods. Is there a way that
in pure SQL (without PL SQL and a cursor) I can calculate

STOCK of PERIOD (N) = STOCK of PERIOD (N-1) * (1-CHURN_RATE) +
NEW_UNITS

?

Thanks and best,

Hans

Hans Mayr escribió:
Perhaps you can use the LAG function:

http://www.adp-gmbh.ch/ora/sql/analytical/lag.html


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On 14 Aug., 14:21, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
Hi,

no, I don't think so. Because the lag goes to a fiel in the preceeding
line of a certain object. But I need to refer to a calculation,
actually the calculation has to refer to itself. Maybe I was not clear
but I do not only want to calculate the Stock for one following period
but for many. So the result n has to become the source of result n+1
etc.

If I did not express myself clearly enough, please tell me.

Best,

Hans

"Hans Mayr" <mayr1972 (AT) gmx (DOT) de> a écrit dans le message de news: 175b9dc2-db4a-4fab-855a-276439402249...oglegroups.com...
If you post a test case (create table and insert statements) maybe we can help more.
It seems to be a good example for MODEL clause although it may be possible to do
it with CONNECT BY depending on the exact case.

Regards
Michel

"Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news: 4a857ab2$0$287$426a74cc (AT) news (DOT) free.fr...
See the discussion in the following thread:
http://www.orafaq.com/forum/mv/msg/1...89/#msg_350586

Regards
Michel