This discussion is archived
13 Replies Latest reply: Dec 15, 2008 2:08 AM by 533433 RSS

Substract two values from different rows

675326 Newbie
Currently Being Moderated
Hello. I'm in the middle of a problem.
I have many many rows in a table. I need to add another column to it which has to be subtract of value A from row N and value B from row N-1. For example

. NEW COLUMN
2008-10-10 12:05 223
2008-10-10 12:10 226 3
2008-10-10 12:15 238 12

Etc... How this can be acomplished? Thanks in advance.
  • 1. Re: Substract two values from different rows
    Walter Fernández Expert
    Currently Being Moderated
    Hi,

    Welcome to the forum! :)

    The table has the columns you have shown?

    Date column
    Number column
    Number column (the new)

    Regards,
  • 2. Re: Substract two values from different rows
    533433 Journeyer
    Currently Being Moderated
    You may try the below:
    with t as
    (
    select to_date('2008-10-10 12:05','yyyy-mm-dd hh:mi') col1, 223 col2 from dual union all
    select to_date('2008-10-10 12:10','yyyy-mm-dd hh:mi') col1, 226 col2 from dual union all
    select to_date('2008-10-10 12:15','yyyy-mm-dd hh:mi') col1, 238 col2 from dual
    )
    select col1, col2, col2- (lag(col2) over (order by col2)) newcol from t order by col1, col2
  • 3. Re: Substract two values from different rows
    505805 Newbie
    Currently Being Moderated
    Hi,

    you might want to try this one
    with t as (
     select '2008-10-10 12:05' col1, 223 col2, null col3 from dual union all
     select '2008-10-10 12:10', 226, 3 from dual union all
     select '2008-10-10 12:15', 238, 12 from dual 
    )
    select t.*, 
      nvl(col3,0)-lag(nvl(col3,0),1,0) over (order by col1,col2) col4
    from t
    
    3 rows selected
    
    COL1             COL2                   COL3                   COL4                   
    ---------------- ---------------------- ---------------------- ---------------------- 
    2008-10-10 12:05 223                                           0                      
    2008-10-10 12:10 226                    3                      3                      
    2008-10-10 12:15 238                    12                     9                      
    
    3 rows selected
    Edited by: nurhidayat on Dec 12, 2008 7:05 PM

    @Walter,
    Sorry, I should have replied to user2972890, not you

    @user2972890
    I guess i have misread your post, but the idea is the same as APNL solution,
    The column will be generated whenever new row has been added

    Cheers
    [Nur Hidayat|http://nur-hidayat.net]
  • 4. Re: Substract two values from different rows
    675326 Newbie
    Currently Being Moderated
    Hello. Thanks for the answers. The column has to be generated whenever new row is being added. So for new row, the NEW_COLUMN has to generate value of that subtraction. APNL - that would be good if I have not many rows and static table, but this is generated in 5 minute periods.

    My previous post mugt be confusing, but data is like: DATE, VALUES, NEW_COLUMN_SUBTRACTION

    Edited by: user2972890 on 2008-12-12 04:07
  • 5. Re: Substract two values from different rows
    NavneetU Expert
    Currently Being Moderated
    You need to create a trigger for this . The resulting column will get values through the trigger whenever you insert any new row.

    Regards
  • 6. Re: Substract two values from different rows
    533433 Journeyer
    Currently Being Moderated
    Then probable you can use the below select, every time you insert a new record into this table:
    with t as
    (
    select to_date('2008-10-10 12:05','yyyy-mm-dd hh:mi') col1, 223 col2 from dual union all
    select to_date('2008-10-10 12:10','yyyy-mm-dd hh:mi') col1, 226 col2 from dual union all
    select to_date('2008-10-10 12:15','yyyy-mm-dd hh:mi') col1, 238 col2 from dual
    )
    select to_date('2008-10-10 12:20','yyyy-mm-dd hh:mi') col1, 242 col2,242- (select max(col2) from t where col2 < 242) newcol from dual
    as
    insert into table (col1, col2, newcol) values (to_date('2008-10-10 12:20','yyyy-mm-dd hh:mi') , 242 , 242 -  (select max(col2) from t where col2 < 242));
  • 7. Re: Substract two values from different rows
    675326 Newbie
    Currently Being Moderated
    Hi, thanks for that solution. I'm not a big expert in PL SQL, so I might just don't get the idea.

    I have a row with DATE and VALUE column. Assume the new row appers with values: 2008-10-10 12:20; 250
    The trigger needs to insert into that row and in column SUBTRACT a new value, ie. 10 which will be subtraction of 250 and value from previous row. APNL - your code will do that, right?
  • 8. Re: Substract two values from different rows
    533433 Journeyer
    Currently Being Moderated
    Yes indeed. You can try with trigger as below:
    SQL> create table t as
      2  select to_date('2008-10-10 12:05','yyyy-mm-dd hh:mi') col1, 223 col2 from dual union all
      3  select to_date('2008-10-10 12:10','yyyy-mm-dd hh:mi') col1, 226 col2 from dual union all
      4  select to_date('2008-10-10 12:15','yyyy-mm-dd hh:mi') col1, 238 col2 from dual;
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER test_trigger BEFORE INSERT ON t
      2  FOR EACH ROW
      3  DECLARE
      4  newval NUMBER;
      5  BEGIN
      6  SELECT NVL(MAX(COL2),0) INTO newval FROM T WHERE COL2 < :new.COL2;
      7  :new.newcol:=:new.COL2 - newval;
      8* END;
    SQL> /
    
    Trigger created.
                                                                                                                                                                       SQL> insert into t values (to_date('2008-10-10 12:20','yyyy-mm-dd hh:mi'),242,0);
    
    1 row created.
    
    SQL> select col1, col2, newcol from t;
    
    COL1            COL2     NEWCOL                                                                                               
    --------- ---------- ----------                                                                                               
    10-OCT-08        223                                                                                                          
    10-OCT-08        226                                                                                                          
    10-OCT-08        238                                                                                                          
    10-OCT-08        242        4                                                                                               
  • 9. Re: Substract two values from different rows
    RobvanWijk Oracle ACE
    Currently Being Moderated
    APNL wrote:
    You can try with trigger as below:
    I wouldn't propose a solution with a mutating table bug:
    SQL> create table t
      2  as
      3  select to_date('2008-10-10 12:05','yyyy-mm-dd hh:mi') col1, 223 col2 from dual union all
      4  select to_date('2008-10-10 12:10','yyyy-mm-dd hh:mi') col1, 226 col2 from dual union all
      5  select to_date('2008-10-10 12:15','yyyy-mm-dd hh:mi') col1, 238 col2 from dual
      6  /
    
    Tabel is aangemaakt.
    
    SQL> alter table t add (newcol number)
      2  /
    
    Tabel is gewijzigd.
    
    SQL> CREATE OR REPLACE TRIGGER test_trigger BEFORE INSERT ON t
      2  FOR EACH ROW
      3  DECLARE
      4    newval NUMBER;
      5  BEGIN
      6    SELECT NVL(MAX(COL2),0) INTO newval FROM T WHERE COL2 < :new.COL2;
      7    :new.newcol:=:new.COL2 - newval;
      8  END;
      9  /
    
    Trigger is aangemaakt.
    
    SQL> insert into t (col1,col2)
      2  values (to_date('2008-10-10 12:20','yyyy-mm-dd hh:mi'),242)
      3  /
    
    1 rij is aangemaakt.
    
    SQL> select col1, col2, newcol from t
      2  /
    
    COL1                      COL2     NEWCOL
    ------------------- ---------- ----------
    10-10-2008 12:05:00        223
    10-10-2008 12:10:00        226
    10-10-2008 12:15:00        238
    10-10-2008 12:20:00        242          4
    
    4 rijen zijn geselecteerd.
    
    SQL> insert into t (col1,col2)
      2  select to_date('2008-10-10 12:25','yyyy-mm-dd hh:mi')
      3       , 250
      4    from dual
      5  /
    insert into t (col1,col2)
                *
    FOUT in regel 1:
    .ORA-04091: Tabel RWIJK.T wordt gemuteerd en mag niet door trigger/functie worden benaderd.
    ORA-06512: in "RWIJK.TEST_TRIGGER", regel 4
    ORA-04088: Fout bij uitvoering van trigger 'RWIJK.TEST_TRIGGER'.
    Regards,
    Rob.
  • 10. Re: Substract two values from different rows
    RobvanWijk Oracle ACE
    Currently Being Moderated
    This is how I would do it, supposing all 5 minute values come chronologically, one at a time:
    SQL> create table t
      2  as
      3  select to_date('2008-10-10 12:05','yyyy-mm-dd hh:mi') col1, 223 col2 from dual union all
      4  select to_date('2008-10-10 12:10','yyyy-mm-dd hh:mi') col1, 226 col2 from dual union all
      5  select to_date('2008-10-10 12:15','yyyy-mm-dd hh:mi') col1, 238 col2 from dual
      6  /
    
    Tabel is aangemaakt.
    
    SQL> alter table t add (newcol number)
      2  /
    
    Tabel is gewijzigd.
    
    SQL> create procedure add_new_5_minute_value (p_value in t.col2%type)
      2  as
      3  begin
      4    insert into t
      5    ( col1
      6    , col2
      7    , newcol
      8    )
      9    select max(col1) + interval '5' minute
     10         , p_value
     11         , p_value - max(col2) keep (dense_rank last order by col1)
     12      from t
     13    ;
     14  end add_new_5_minute_value;
     15  /
    
    Procedure is aangemaakt.
    
    SQL> exec add_new_5_minute_value(242)
    
    PL/SQL-procedure is geslaagd.
    
    SQL> exec add_new_5_minute_value(250)
    
    PL/SQL-procedure is geslaagd.
    
    SQL> select col1, col2, newcol from t
      2  /
    
    COL1                      COL2     NEWCOL
    ------------------- ---------- ----------
    10-10-2008 12:05:00        223
    10-10-2008 12:10:00        226
    10-10-2008 12:15:00        238
    10-10-2008 12:20:00        242          4
    10-10-2008 12:25:00        250          8
    
    5 rijen zijn geselecteerd.
    Regards,
    Rob.
  • 11. Re: Substract two values from different rows
    533433 Journeyer
    Currently Being Moderated
    Thanks for the feedback Rob. This seems to be quite interesting. I am still unable to understand why the error message +"is mutating, trigger/function may not see it"+ is thrown upon issuing :
    insert into t (col1,col2)
    select to_date('2008-10-10 12:25','yyyy-mm-dd hh:mi')
    , 250
    from dual
    coz, I dont get any error message when I use the folowing insert statement instead:
    insert into t (col1,col2) values (to_date('2008-10-10 12:25','yyyy-mm-dd hh:mi'),250);
    -- Cheers
  • 12. Re: Substract two values from different rows
    RobvanWijk Oracle ACE
    Currently Being Moderated
    You get a mutating table error, because you are selecting from the same table in your before row insert trigger. If this was allowed, you would be able to see the results of the database half-way through an insert. For example when 5 rows out of 10 are inserted. Oracle prevents you from seeing this "dirty" data.

    The INSERT VALUES clause is an exception, because using that syntax, Oracle knows that exactly 1 row is inserted. So either you see the data before this row is inserted, before the statement, or after the statement. It is not possible to see the results of the insert half-way. And so Oracle allows this.

    But the trigger as you wrote it, has introduced a bug that makes it impossible to use the INSERT SELECT syntax.

    Regards,
    Rob.
  • 13. Re: Substract two values from different rows
    533433 Journeyer
    Currently Being Moderated
    Thanks Rob for the detailed explanation :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points