Home » SQL & PL/SQL » SQL & PL/SQL » Need logic for row1 - row2 (Oracle 9i)
Need logic for row1 - row2 [message #279755] Sat, 10 November 2007 03:05 Go to next message
oraebs11i
Messages: 4
Registered: October 2007
Junior Member
Hi Friends,

I have a requirement like following:

Column1 Column2
========= =======

1000 0 (1000-1000)
2000 1000 (2000-1000)
3500 1500 (3500-2000)
5500 2000 (5500-3500)

Here I have only Column1, I need to find the subtraction.

Thanks in Advance.
Rahul

[Updated on: Sat, 10 November 2007 03:27]

Report message to a moderator

Re: Need logic for row1 - row2 [message #279763 is a reply to message #279755] Sat, 10 November 2007 03:34 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use the LAG function:
SQL> SELECT col1, lag(col1) over (ORDER BY col1) col2,
  2    col1 - lag(col1) over (ORDER BY col1) diff
  3  FROM TEST;

      COL1       COL2       DIFF
---------- ---------- ----------
      1000
      2000       1000       1000
      3500       2000       1500
      5500       3500       2000
Re: Need logic for row1 - row2 [message #279776 is a reply to message #279763] Sat, 10 November 2007 03:45 Go to previous messageGo to next message
oraebs11i
Messages: 4
Registered: October 2007
Junior Member
Hi,

Thanks for your reply. I need to get 0(ZER0) in the first row.

1000 - 1000 = 0

Thanks in advance
Re: Need logic for row1 - row2 [message #279777 is a reply to message #279776] Sat, 10 November 2007 03:47 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ever heard of the NVL function? If not, this is the time to learn more about it.
Previous Topic: DUP_VAL_ON_INDEX (Which Index ?)
Next Topic: Tablename as parameter. How to use it in the SQL Statement in the PL/SQL Code?
Goto Forum:
  


Current Time: Sun Dec 04 06:38:47 CST 2016

Total time taken to generate the page: 0.06110 seconds