Home » SQL & PL/SQL » SQL & PL/SQL » Diagonal Subtraction of Column Values
Diagonal Subtraction of Column Values [message #201595] Mon, 06 November 2006 00:25 Go to next message
amit.pandey
Messages: 64
Registered: 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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: 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 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: 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 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: 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" Wink
Re: Diagonal Subtraction of Column Values [message #201630 is a reply to message #201627] Mon, 06 November 2006 02:37 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: 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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: 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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1578/0/ 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 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: 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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're too kind Cool

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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: 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 
instead of a*case
Re: Diagonal Subtraction of Column Values [message #201670 is a reply to message #201637] Mon, 06 November 2006 05:29 Go to previous message
amit.pandey
Messages: 64
Registered: 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: Fri Dec 09 17:14:16 CST 2016

Total time taken to generate the page: 0.32480 seconds