Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

conditional incremensum (condition is on the sum value of the previous row)

518921Jan 28 2010 — edited Feb 2 2010
Hi,
i know is a little bit tricky but i need to know the credit of a client after each invoice.
so if sum(invoice_val-previous_credit) >0 then the credit is 0 else credit is previous_credit-invoice_val.
As you can see to dermine the credit value of the current row it is needed to check the same value of the previous row.Is this possible in some way?

EX.
so the situation in the table is the following: 
ID_CLIENT	ID_invoice	invoice_VAL
8.789	220.227	120,47
8.789	238.342	109,76
8.789	246.388	121,69
8.789	258.163	137,45
8.789	268.969	138,67
8.789	295.455	145,16
8.789	311.395	138,92
8.789	327.104	138,96
8.789	340.793	-335,18
8.789	375.451	129,14
8.789	386.650	125,57
8.789	398.606	124,18
8.789	428.166	31,66
8.789	435.844	25,93
8.789	447.639	34,32
8.789	462.137	-43,64
8.789	475.613	-110,39
8.789	485.022	92,29
8.789	495.807	91,67
i need something like this: 
ID_CLIENT	ID_invoice	invoice_VAL credit
8.789	220.227	120,47	0,00
8.789	238.342	109,76	0,00
8.789	246.388	121,69	0,00
8.789	258.163	137,45	0,00
8.789	268.969	138,67	0,00
8.789	295.455	145,16	0,00
8.789	311.395	138,92	0,00
8.789	327.104	138,96	0,00
8.789	340.793	-335,18	335,18
8.789	375.451	129,14	206,04
8.789	386.650	125,57	80,47
8.789	398.606	124,18	0,00
8.789	428.166	31,66	0,00
8.789	435.844	25,93	0,00
8.789	447.639	34,32	0,00
8.789	462.137	-43,64	43,64
8.789	475.613	-110,39	154,03
8.789	485.022	92,29	61,74
8.789	495.807	91,67	0,00
thanks very much

Edited by: 4ndr34 on Jan 28, 2010 1:38 PM
This post has been answered by Frank Kulash on Jan 29 2010
Jump to Answer

Comments

Cuauhtemoc Amox
By name =)? You can have a package specification only with no implementation, and so as You guess there may be some internal mechanism but I haven't seen it documented on user guides. Perhaps DBA's may have knowledge on how those structures are stored internally, as the parsed and compiled code is stored and loaded for execution, not the text we see in user|all|dba_source.
damorgan

One way:

SELECT name, referenced_name, referenced_type
FROM dba_dependencies
WHERE name = 'UTL_FILE'
AND referenced_type like '%PACKAGE%'
UNION
SELECT name, referenced_name, referenced_type
FROM dba_dependencies
WHERE referenced_name = 'UTL_FILE'
AND referenced_type like '%PACKAGE%';
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 2 2010
Added on Jan 28 2010
11 comments
2,608 views