Re: Retrive values from different rows

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sun, 17 Jun 2007 14:51:43 GMT
Message-ID: <3Ubdi.3774$s57.2444@newsfe07.phx>

sviluppo.sw_at_gmail.com wrote in news:1182090432.657924.152450 @m36g2000hse.googlegroups.com:

> Hi,
> I've this intriguing problem.
>

>>From a table like this


> ID     ART     QTA     REASON

> 1     A     10     W

> 2     A     -2     C

> 3     A     3     PO

> 4     A     -4     C

> 5     B     5     W

> 6     B     -2     C

> 7     B     -4     C

> 8     B     5     PO

> 9     C     5     W

> 10     D     7     W

> 11     ...     ...     ...

> 12     ...     ...     ...

>
> where:
> W=stock level
> C=consuption
> PO=purchase order
>
> I want to obtain a new column VAL with the, so called, "dynamic-
> whareouse".
> The value in the VAL column must be equal to QTA when reason is W.
> In the other cases, the value of VAL must be
> the sum of the value of VAL in the n-1row and the QTA value of the
> the n row.
>
> In other words this must be the result:
> ID ART QTA REASON VAL
> 1 A 10 W 10
> 2 A -2 C 8
> 3 A 3 PO 11
> 4 A -4 C 7
> 5 B 5 W 5
> 6 B -2 C 3
> 7 B -4 C -1
> 8 B 5 PO 4
> 9 C 5 W 5
> 10 D 7 W 7
> 11 ... ... ... ...
> 12 ... ... ... ...
>
> It' esay by Excel but I've not found any way in Oracle (9i).
> Can someone help me?
> Thanks in advance
> Marco
>
>

You have a fundamental misconception!
SQL returns a set (collection) of rows which have NO fixed order; [unless & until the ORDER BY clause is part of the query]. Therefore there is no such thing as N-1 row.

HTH & YMMV Received on Sun Jun 17 2007 - 09:51:43 CDT