Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
conditional incremensum (condition is on the sum value of the previous row)

518921
Member Posts: 12
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:
Edited by: 4ndr34 on Jan 28, 2010 1:38 PM
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,67i 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,00thanks very much
Edited by: 4ndr34 on Jan 28, 2010 1:38 PM
Best Answer
-
Hi,4ndr34 wrote:Did you mention that before?
Thanks but model is not available in oracle9
It's a good idea to include your version number whenever you ask a question, especially if the version is as old as Oracle 9.
Here's an analytic solution that should work in Oracle 9 (and up):WITH e AS ( SELECT hiredate , CASE WHEN job IN ('PRESIDENT', 'SALESMAN') THEN -sal ELSE sal END AS sal2 , ROW_NUMBER () OVER ( ORDER BY hiredate , empno ) AS rnum FROM scott.emp ) , got_grp_start AS ( SELECT h.* , CASE WHEN sal2 >= 0 THEN 0 WHEN EXISTS ( SELECT NULL FROM e l JOIN e m ON m.rnum >= l.rnum WHERE l.rnum < h.rnum + 0 -- See note below AND m.rnum < h.rnum + 0 -- See note below GROUP BY l.rnum HAVING SUM (m.sal2) < 0 ) THEN 0 ELSE 1 END AS grp_start FROM e h ) , got_grp AS ( SELECT got_grp_start.* , SUM (grp_start) OVER (ORDER BY rnum) AS grp FROM got_grp_start ) SELECT hiredate , sal2 , GREATEST ( SUM (-sal2) OVER ( PARTITION BY grp ORDER BY rnum ) , 0 ) AS credit FROM got_grp ORDER BY rnum ;
The EXISTS sub-query in got_grp_start doesn't work correctly (at least in Oracle 11.1.0.6.0 or 10.2.0.3.0) if it references "h.rnum", but it does if it references "h.rnum+0" or "h.rnum-0".
The only purpose of sub-query e is to produce a useful set of raw data, including a simple expression (rnum) that can be used in sorting. You probably don't need anything like e, depending on your actual table.
If you need a separate calculation for, say, every department, then add PARTITION BY to the analytic clauses.
Answers
-
To get the previous value you can try using LAG analytical function.
-
it is not so simple....
if you could read carefully, you'll understand!!!! -
Hi,
You meantselect ID_CLIENT , ID_invoice , invoice_VAL , decode(sign(c_credit),1,0,-c_credit) credit from( select ID_CLIENT, ID_invoice , invoice_VAL, sum(invoice_VAL) over (partition by ID_CLIENT order by ID_invoice) c_credit from t1 );
Regards
Anurag -
i have tried something similar , but i have tried also copying your query.
i always get 0 as credit because the check is on the credit obtained considering all the (previous) invoices, instead i do not have to consider the invoices >0 if i have credit =0.
so if i had
100
100
-200
100
150
i should get
100->0
100->0
-200->200
100->100 (200 as previous credit - current invoice)
150->0 (100 as previous credit - current invoice, but i don't want to track the debit of the customers, so ->zero)
i hope i have been clear... -
Hi,
I think MODEL is the best way to do this.
Since you didn't post CREATE TABLE and INSERT statements, I'll use the scott.emp table to illustrate.
Say we want to get these results from the scott.emp table:HIREDATE SAL2 CREDIT --------- ---------- ---------- 17-DEC-80 800 0 20-FEB-81 -1600 1600 22-FEB-81 -1250 2850 02-APR-81 2975 0 01-MAY-81 2850 0 09-JUN-81 2450 0 08-SEP-81 -1500 1500 28-SEP-81 -1250 2750 17-NOV-81 -5000 7750 03-DEC-81 950 6800 03-DEC-81 3000 3800 23-JAN-82 1300 2500 19-APR-87 3000 0 23-MAY-87 1100 0
Where the results are in order by hirdate (then empno, if there is a tie).
Sal2 is sal or -sal, depending on each employee's job.
Credit is the previous row's credit, minus sal2, but never less than 0.
Here's one way to compute credit using MODEL:WITH e AS ( SELECT hiredate , CASE WHEN job IN ('PRESIDENT', 'SALESMAN') THEN -sal ELSE sal END AS sal2 , ROW_NUMBER () OVER ( ORDER BY hiredate , empno ) AS rnum FROM scott.emp ) SELECT hiredate , sal2 , credit FROM e MODEL DIMENSION BY (rnum) MEASURES ( hiredate , sal2 , 0 credit ) RULES ( credit [ANY] ORDER BY rnum = NVL ( GREATEST ( credit [CV () - 1] - sal2 [CV ()] , 0 ) , GREATEST (-sal2 [CV()], 0) ) ) ORDER BY hiredate ;
To experiment with this, you can change the jobs that result in negative sal2s. For example, include 'CLERK' to see what happens when the first row has a credit.
You can get the same results using the analytic SUM function, but you have to PARTITION BY a group that starts with a negative sal2, and continues until credit = 0. Computing those groups requires other analytic functions, and it's much more complicated than the MODEL solution.
You can also get these results using CONNECT BY.
Using either of these last two techniques might be a good, challenging exercise for someone interested in analytic functions or CONNECT BY.
Edited by: Frank Kulash on Jan 28, 2010 10:32 AM -
Thanks but model is not available in oracle9
may i have an aid in the alternative ways you mentioned?
Edited by: 4ndr34 on Jan 28, 2010 5:42 PM -
Hi,4ndr34 wrote:Did you mention that before?
Thanks but model is not available in oracle9
It's a good idea to include your version number whenever you ask a question, especially if the version is as old as Oracle 9.
Here's an analytic solution that should work in Oracle 9 (and up):WITH e AS ( SELECT hiredate , CASE WHEN job IN ('PRESIDENT', 'SALESMAN') THEN -sal ELSE sal END AS sal2 , ROW_NUMBER () OVER ( ORDER BY hiredate , empno ) AS rnum FROM scott.emp ) , got_grp_start AS ( SELECT h.* , CASE WHEN sal2 >= 0 THEN 0 WHEN EXISTS ( SELECT NULL FROM e l JOIN e m ON m.rnum >= l.rnum WHERE l.rnum < h.rnum + 0 -- See note below AND m.rnum < h.rnum + 0 -- See note below GROUP BY l.rnum HAVING SUM (m.sal2) < 0 ) THEN 0 ELSE 1 END AS grp_start FROM e h ) , got_grp AS ( SELECT got_grp_start.* , SUM (grp_start) OVER (ORDER BY rnum) AS grp FROM got_grp_start ) SELECT hiredate , sal2 , GREATEST ( SUM (-sal2) OVER ( PARTITION BY grp ORDER BY rnum ) , 0 ) AS credit FROM got_grp ORDER BY rnum ;
The EXISTS sub-query in got_grp_start doesn't work correctly (at least in Oracle 11.1.0.6.0 or 10.2.0.3.0) if it references "h.rnum", but it does if it references "h.rnum+0" or "h.rnum-0".
The only purpose of sub-query e is to produce a useful set of raw data, including a simple expression (rnum) that can be used in sorting. You probably don't need anything like e, depending on your actual table.
If you need a separate calculation for, say, every department, then add PARTITION BY to the analytic clauses. -
thanks you are a monster:D
Eventually, i decided to solve with a function that return the credit.
however, thanks very much for your time -
4ndr34 wrote:Out of curiosity, how is your function coded?
thanks you are a monster:D
Eventually, i decided to solve with a function that return the credit.
however, thanks very much for your time
I don't think that doing it that way would be more efficient than including the calculations in one SQL statement, such as the ones Frank came up with! I'd be interested to hear how the SQL solution and the function solution compare performance wise when run against proper levels of data. -
hi!
i did it with a function before Frank's second answer, so, not for choice but just because it was the only way i could do it.
probably (surely), Frank's way is the best one if i needed the complete result (credit after each invoice, for each client) , but i need (every time i launch the query) only the balance (credit) after the last invoice for each client ( i have a list i can launch the query against).
the function is as follow:CREATE OR REPLACE Function credito ( id_fattura_in IN number ) --id invoice RETURN number IS saldo number; -- credit VAL_FATTURA number; --invoice value id_sito_p number; -- id contract/client cursor c1 (id_sito_in in number) is SELECT VAL_TOT_FATTURA FROM T016_FATTURE_TESTATE WHERE ID_sito= id_sito_in AND id_fattura< id_fattura_IN and cod_tipo_fattura='NOR' ORDER BY id_FATTURA; BEGIN select id_sito into id_sito_p from t016_fatture_testate where id_fattura=id_fattura_in; SALDO:= 0; open c1(id_sito_p); LOOP FETCH C1 INTO VAL_FATTURA; EXIT WHEN C1%NOTFOUND; SALDO:=LEAST(SALDO+VAL_FATTURA,0); END LOOP; close c1; RETURN saldo; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Errore: - '||SQLCODE||' -ERROR- '||SQLERRM); END; /
This discussion has been closed.