Home » SQL & PL/SQL » SQL & PL/SQL » Diagonal Subtraction of Column Values
Diagonal Subtraction of Column Values Mon, 06 November 2006 00:25
 amit.pandey Messages: 64Registered: August 2006 Location: Bangalore, India Member
Hi ,

I've a table whose data is mentioned here.

A B
NULL 100
300 NULL
600 NULL
800 NULL
1900 NULL

what i want is that b values should be populated, by the value of next row of A - value of b of current row, by using a simple SQL.

Result should be look like this

A B
NULL 100
300 200 (exp: 300 -100)
600 400 (exp: 600 - 200)
800 400 (exp: 800 - 400)
1900 1500 (exp: 1900 - 400)

If you have any solution, kindly let me know the same.
Regards!
Re: Diagonal Subtraction of Column Values [message #201620 is a reply to message #201595] Mon, 06 November 2006 02:01
 Frank Messages: 7880Registered: March 2000 Senior Member
Sounds like a homework assignment.
Why don't you show us how far you got first, then we will help you further from the point where you are stuck.
Re: Diagonal Subtraction of Column Values [message #201627 is a reply to message #201620] Mon, 06 November 2006 02:33
 amit.pandey Messages: 64Registered: August 2006 Location: Bangalore, India Member
Sorry, If it seems like an homework.

I've tried rownum to get the solution, but problem is that, with rownum we cannot use > , and i'd not idea how to solve this query, that's y i put it here.

Regards
Amit
Re: Diagonal Subtraction of Column Values [message #201629 is a reply to message #201627] Mon, 06 November 2006 02:36
 ThomasG Messages: 3202Registered: April 2005 Location: Heilbronn, Germany Senior Member
A hint from the "Analytical Functions in Oracle" Documentation :

"Lag/Lead functions make it possible to access values in other rows than the current one"
Re: Diagonal Subtraction of Column Values [message #201630 is a reply to message #201627] Mon, 06 November 2006 02:37
 ehegagoka Messages: 493Registered: July 2005 Senior Member
hi!
i think analytic functions can handle this =) im also still reading on it, check on LAG and LEAD if it would solve it.
Re: Diagonal Subtraction of Column Values [message #201637 is a reply to message #201630] Mon, 06 November 2006 03:06
 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member
Here's a partial solution.
If you change the two values round in the first column, you can generate the solution like this:

```create table table_c (a number, b number);

insert into table_c values (100,null);
insert into table_c values (300,null);
insert into table_c values (600,null);
insert into table_c values (800,null);
insert into table_c values (1900,null);

select a,abs(sum(signed) over (order by rnum rows between unbounded preceding and current row)) new_b
from (
select a,b,a*case when mod(rnum,2)=0 then 1 else -1 end signed,rnum
from  (select a,b,row_number() over (order by nvl(a,0)) rnum
from   table_c)
);

A      NEW_B
---------- ----------
100        100
300        200
600        400
800        400
1900       1500```
Re: Diagonal Subtraction of Column Values [message #201642 is a reply to message #201637] Mon, 06 November 2006 03:22
 Littlefoot Messages: 21130Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
Even if I'd live 1E6 years, I wouldn't know how to write something like this.
Re: Diagonal Subtraction of Column Values [message #201647 is a reply to message #201642] Mon, 06 November 2006 03:28
 ehegagoka Messages: 493Registered: July 2005 Senior Member
hi,
me too!!!, sir JRowbottom really is an EXPERT!!!
Re: Diagonal Subtraction of Column Values [message #201652 is a reply to message #201647] Mon, 06 November 2006 03:50
 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member
You're too kind

All it's doing is to give the value for column A an alternating +/- value (the CASE statement), and then sum up these values up to the current row (that's the ROWS BETWEEN bit).
If you make the resulting value positive, then you get the answer we're looking for.

Re: Diagonal Subtraction of Column Values [message #201658 is a reply to message #201647] Mon, 06 November 2006 03:57
 Frank Messages: 7880Registered: March 2000 Senior Member
Nice one JRowbottom!
Didn't look so hard at first glance, but I forgot that lag(b) would not propagate the calculated value.

To use the OP's data, change this in the inner query:

```(nvl(a, 0) + nvl(b, 0))*case
```
Re: Diagonal Subtraction of Column Values [message #201670 is a reply to message #201637] Mon, 06 November 2006 05:29
 amit.pandey Messages: 64Registered: August 2006 Location: Bangalore, India Member
Oh God!!

You are amazing!
wow what a query and thinking .. nice man ..
Thanx a lot ..

i'll go now by step and step ..

Thanks & regards
Amit
 Previous Topic: Need Query Next Topic: SQL function to Parse XML document stored in BLOB
Goto Forum:

Current Time: Wed Jul 26 16:03:03 CDT 2017

Total time taken to generate the page: 0.11585 seconds