Home » SQL & PL/SQL » SQL & PL/SQL » row-to-row navigation query
row-to-row navigation query [message #665970] Thu, 05 October 2017 02:34 Go to next message
martjosh
Messages: 13
Registered: December 2009
Location: Riyadh
Junior Member
Hi Experts,

Need your help.

create table test_case (id varchar2(10),num number,c1 number,c2 number);

Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',1,7140,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',2,-14545,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',3,-36230,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',4,-57915,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',5,-79600,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',6,-101285,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',7,-122970,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',8,-144655,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',9,-166340,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',10,-188025,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',11,-209710,21685);

I need the out put like this --

ID	NUM	C1	C2
------------------------------
12345	1	7140	21685
12345	2	-14545	21685
12345	3	-36230	21685
12345	4	-57915	21685
12345	5	-79600	21685
12345	6	-101285	21685
12345	7	-122970	21685
12345	8	-144655	21685
12345	9	-166340	21685
12345	10	-188025	21685
12345	11	-209710	21685

logic
------
row 2(C1)= row1(C1)- row1(C2) --> 7140 - 21685
row 3(C1)= row2(C1)- row2(C2) --> -14545 - 21685
row 4(C1)= row3(C1)- row3(C2) --> -36230 - 21685
..
..
and so on

--moderator update: I've added [code] tags, please do so yourself in future.

[Updated on: Thu, 05 October 2017 02:56] by Moderator

Report message to a moderator

Re: row-to-row navigation query [message #665971 is a reply to message #665970] Thu, 05 October 2017 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select id, num,
  2         lag(c1) over (partition by id order by num)-lag(c2) over (partition by id order by num) c1,
  3         c2
  4  from TEST_CASE
  5  order by id, num
  6  /
ID                NUM         C1         C2
---------- ---------- ---------- ----------
12345               1                 21685
12345               2     -14545      21685
12345               3     -36230      21685
12345               4     -57915      21685
12345               5     -79600      21685
12345               6    -101285      21685
12345               7    -122970      21685
12345               8    -144655      21685
12345               9    -166340      21685
12345              10    -188025      21685
12345              11    -209710      21685

11 rows selected.
Re: row-to-row navigation query [message #665972 is a reply to message #665970] Thu, 05 October 2017 02:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've split your post off from the topic you hijacked and given it what I hope is a meaningful title.

with regard to your query, I don't understand what you want at all. Get the output you give, all that is needed is this:
orclx> select * from test_case order by 2;

ID                NUM         C1         C2
---------- ---------- ---------- ----------
12345               1       7140      21685
12345               2     -14545      21685
12345               3     -36230      21685
12345               4     -57915      21685
12345               5     -79600      21685
12345               6    -101285      21685
12345               7    -122970      21685
12345               8    -144655      21685
12345               9    -166340      21685
12345              10    -188025      21685
12345              11    -209710      21685

11 rows selected.

orclx>
Re: row-to-row navigation query [message #665974 is a reply to message #665972] Thu, 05 October 2017 04:27 Go to previous messageGo to next message
martjosh
Messages: 13
Registered: December 2009
Location: Riyadh
Junior Member
Sorry my mistakes. Initial values should be.

ID NUM C1 C2
---------- ---------- ---------- ----------
12345 1 7140 21685
12345 2 7140 21685
12345 3 7140 21685
12345 4 7140 21685
12345 5 7140 21685
12345 6 7140 21685
12345 7 7140 21685
12345 8 7140 21685
12345 9 7140 21685
12345 10 7140 21685
12345 11 7140 21685
Re: row-to-row navigation query [message #665975 is a reply to message #665974] Thu, 05 October 2017 04:42 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
Probably by using the running total?
WITH
    TEST_CASE(ID ,NUM ,C1 ,C2)
    AS
        (SELECT '12345', 1, 7140, 21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
    PREPARE4CALC
    AS
        (SELECT ID
               ,NUM
               ,C1
               ,C2
               ,SUM(C2) OVER(PARTITION BY ID ORDER BY NUM)         RUNNING_TOTAL
           FROM TEST_CASE)
SELECT ID
      ,NUM
      ,C1
      ,C2
      ,C1 - COALESCE(LAG(RUNNING_TOTAL) OVER(PARTITION BY ID ORDER BY NUM), 0) CALC_VALUE
  FROM PREPARE4CALC

Unfortunately you can't encapsulate windows functions, so you have to split it like that.

If you want to go fancy, try a recursive statement Wink
WITH
    TEST_CASE(ID
             ,NUM
             ,C1
             ,C2)
    AS
        (SELECT '12345', 1, 7140, 21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
    PREPARE4CALC
    AS
        (SELECT ID
               ,NUM
               ,C1
               ,C2
               ,COALESCE(LAG(NUM) OVER(PARTITION BY ID ORDER BY NUM), 0) PARENT_NUM
           FROM TEST_CASE),
    CALC(ID
        ,NUM
        ,C1
        ,C2
        ,FULL_PATH
        ,PARENT_NUM
        ,CALC_VALUE)
    AS
        (SELECT ID
               ,NUM
               ,C1
               ,C2
               ,TO_CHAR(NUM)   AS FULL_PATH
               ,PARENT_NUM
               ,C1 AS CALC_VALUE
           FROM PREPARE4CALC
          WHERE NUM = 1
         UNION ALL
         SELECT PREPARE4CALC.ID
               ,PREPARE4CALC.NUM
               ,PREPARE4CALC.C1
               ,PREPARE4CALC.C2
               ,CALC.FULL_PATH || ' --> ' || PREPARE4CALC.NUM AS FULL_PATH
               ,PREPARE4CALC.PARENT_NUM
               ,CALC.CALC_VALUE - PREPARE4CALC.C2
           FROM CALC, PREPARE4CALC
          WHERE CALC.NUM = PREPARE4CALC.PARENT_NUM)
SELECT *
  FROM CALC

[Updated on: Thu, 05 October 2017 05:25]

Report message to a moderator

icon14.gif  Re: row-to-row navigation query [message #665992 is a reply to message #665975] Fri, 06 October 2017 02:05 Go to previous messageGo to next message
martjosh
Messages: 13
Registered: December 2009
Location: Riyadh
Junior Member
Thank you so much. This is what I need. Cheers!! Smile
Re: row-to-row navigation query [message #665998 is a reply to message #665975] Fri, 06 October 2017 07:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
1. RTFM LAG. Your

COALESCE(LAG(RUNNING_TOTAL) OVER(PARTITION BY ID ORDER BY NUM), 0)

is a complicated way of:

LAG(RUNNING_TOTAL,1,0) OVER(PARTITION BY ID ORDER BY NUM)

2. No need to PREPARE4CALC:

WITH TEST_CASE(ID,NUM,C1,C2)
  AS (
      SELECT '12345', 1, 7140, 21685 FROM DUAL UNION ALL
      SELECT '12345' ,2 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,3 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,4 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,5 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,6 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,7 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,8 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,9 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,10 ,7140 ,21685 FROM DUAL UNION ALL
      SELECT '12345' ,11 ,7140 ,21685 FROM DUAL
     )
SELECT  ID,
        NUM,
        C1,
        C2,
        C1 + C2 - SUM(C2) OVER(PARTITION BY ID ORDER BY NUM) CALC_VALUE
  FROM  TEST_CASE
/

ID            NUM         C1         C2 CALC_VALUE
----- ----------- ---------- ---------- ----------
12345           1       7140      21685       7140
12345           2       7140      21685     -14545
12345           3       7140      21685     -36230
12345           4       7140      21685     -57915
12345           5       7140      21685     -79600
12345           6       7140      21685    -101285
12345           7       7140      21685    -122970
12345           8       7140      21685    -144655
12345           9       7140      21685    -166340
12345          10       7140      21685    -188025
12345          11       7140      21685    -209710

11 rows selected.

SQL> 

SY.
Re: row-to-row navigation query [message #666021 is a reply to message #665970] Mon, 09 October 2017 02:19 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
Solomon Yakobson wrote on Fri, 06 October 2017 07:30
1. RTFM LAG. Your
... snip ...

is a complicated way of:
... snip ...
Thanks for the correction. I'm always keen to learn.

Solomon Yakobson wrote on Fri, 06 October 2017 07:30

2. No need to PREPARE4CALC:
... snip ...

SY.

His requested logic was:martjosh wrote on Thu, 05 October 2017 02:34
logic
------
row 2(C1)= row1(C1)- row1(C2) --> 7140 - 21685
row 3(C1)= row2(C1)- row2(C2) --> -14545 - 21685
row 4(C1)= row3(C1)- row3(C2) --> -36230 - 21685
..
..
and so on
So in reality I even "forgot" the LAG() for the first C1.
But if I'd done that the result would have been:
WITH
    TEST_CASE(ID ,NUM ,C1 ,C2)
    AS
        (SELECT '12345', 1, 7140, 21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
    PREPARE4CALC
    AS
        (SELECT ID
               ,NUM
               ,C1
               ,C2
               ,SUM(C2) OVER(PARTITION BY ID ORDER BY NUM)         RUNNING_TOTAL
           FROM TEST_CASE)
SELECT ID
      ,NUM
      ,C1
      ,C2
      ,LAG(C1,1,0) OVER(PARTITION BY ID ORDER BY NUM) - LAG(RUNNING_TOTAL,1,0) OVER(PARTITION BY ID ORDER BY NUM) CALC_VALUE
  FROM PREPARE4CALC;

ID           NUM         C1         C2 CALC_VALUE
----- ---------- ---------- ---------- ----------
12345          1       7140      21685          0   <-- this must be 7140
12345          2       7140      21685     -14545
12345          3       7140      21685     -36230
12345          4       7140      21685     -57915
12345          5       7140      21685     -79600
12345          6       7140      21685    -101285
12345          7       7140      21685    -122970
12345          8       7140      21685    -144655
12345          9       7140      21685    -166340
12345         10       7140      21685    -188025
12345         11       7140      21685    -209710

11 rows selected.
which did not match to his desired result. So I skipped the first LAG (on C1) in anticipatory obedience.

But while playing around with his example I figured that it felt strange to have in C1 always the same redundant data. That's why I suspected, that he just wants to subtract the previous C2 from a given / inital value (which he repeats in C1 over and over again). That's why I came up with the second solution which might a little bit over shot Wink.

In the end he seems to be happy with what he's got.

Anyway. Thanks again for your correction. I'll incorporate that knowledge in future solutions Wink
Re: row-to-row navigation query [message #666038 is a reply to message #666021] Tue, 10 October 2017 00:09 Go to previous messageGo to next message
martjosh
Messages: 13
Registered: December 2009
Location: Riyadh
Junior Member
This is great. how about if I want the below output for calc_value.

ID NUM C1 C2 CALC_VALUE
----- ----- ---- ----- ---------
12345 1 7140 21685 7140
12345 2 7140 21685 28825
12345 3 7140 21685 50510
12345 4 7140 21685 72195
12345 5 7140 21685 93880
12345 6 7140 21685 115565
12345 7 7140 21685 137250
12345 8 7140 21685 158935
12345 9 7140 21685 180620
12345 10 7140 21685 202305
12345 11 7140 21685 223990


Logic for CALC_VALUE
row 1(CALC_VALUE)= C1
row 2(CALC_VALUE)= row1(CALC_VALUE)+ row1(C2) --> 7140 + 21685
row 3(CALC_VALUE)= row2(CALC_VALUE)+ row2(C2) --> 28825 + 21685
row 4(CALC_VALUE)= row3(CALC_VALUE)+ row3(C2) --> 50510 + 21685
and so on .....
Re: row-to-row navigation query [message #666039 is a reply to message #666038] Tue, 10 October 2017 01:34 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
martjosh wrote on Tue, 10 October 2017 00:09
This is great. how about if I want the below output for calc_value.

Logic for CALC_VALUE
row 1(CALC_VALUE)= C1
row 2(CALC_VALUE)= row1(CALC_VALUE)+ row1(C2) --> 7140 + 21685
row 3(CALC_VALUE)= row2(CALC_VALUE)+ row2(C2) --> 28825 + 21685
row 4(CALC_VALUE)= row3(CALC_VALUE)+ row3(C2) --> 50510 + 21685
and so on .....
This is exactly what my second statement (the recursive one) does. Just replace the '-' sign with a '+' sign.

And you can replace (as suggested by SY) the COALESCE part with the (better) LAG(<value>, 1, 0) solution.
Re: row-to-row navigation query [message #666044 is a reply to message #666021] Tue, 10 October 2017 06:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
quirks wrote on Mon, 09 October 2017 03:19

So in reality I even "forgot" the LAG() for the first C1.
But if I'd done that the result would have been:
Why are you calculating LAG of C1??? It wasn't in your solution and my comment was on LAG for RUNNING_TOTAL. If you want to use that unnecessary PREPARE4CALC, then:

WITH
    TEST_CASE(ID ,NUM ,C1 ,C2)
    AS
        (SELECT '12345', 1, 7140, 21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
         UNION ALL
         SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
    PREPARE4CALC
    AS
        (SELECT ID
               ,NUM
               ,C1
               ,C2
               ,SUM(C2) OVER(PARTITION BY ID ORDER BY NUM)         RUNNING_TOTAL
           FROM TEST_CASE)
SELECT ID
      ,NUM
      ,C1
      ,C2
      ,C1 - LAG(RUNNING_TOTAL,1,0) OVER(PARTITION BY ID ORDER BY NUM) CALC_VALUE
  FROM PREPARE4CALC
/

ID           NUM         C1         C2 CALC_VALUE
----- ---------- ---------- ---------- ----------
12345          1       7140      21685       7140
12345          2       7140      21685     -14545
12345          3       7140      21685     -36230
12345          4       7140      21685     -57915
12345          5       7140      21685     -79600
12345          6       7140      21685    -101285
12345          7       7140      21685    -122970
12345          8       7140      21685    -144655
12345          9       7140      21685    -166340
12345         10       7140      21685    -188025
12345         11       7140      21685    -209710

11 rows selected.

SQL> 

SY.
Re: row-to-row navigation query [message #666052 is a reply to message #666044] Tue, 10 October 2017 09:26 Go to previous message
quirks
Messages: 82
Registered: October 2014
Member
Solomon Yakobson wrote on Tue, 10 October 2017 06:46
Why are you calculating LAG of C1???
Just to illustrate what it would have meant to follow the requirements of the OP word by word.

Solomon Yakobson wrote on Tue, 10 October 2017 06:46
It wasn't in your solution and my comment was on LAG for RUNNING_TOTAL.
Which is totally fine. As you might have figured, I'm still learning as well (and am misusing this forum to harvest tricky questions for myself to grow on Smile ).

Sincerely
Quirks
Previous Topic: Show multiple count in one result set
Next Topic: Heterogeneous DB error propagating past WHEN OTHERS
Goto Forum:
  


Current Time: Thu Mar 28 08:05:13 CDT 2024