Forum Stats

  • 3,733,249 Users
  • 2,246,737 Discussions
  • 7,856,634 Comments

Discussions

Insert problem

586231
586231 Member Posts: 4
edited July 2007 in SQL & PL/SQL
Hi,

I have the following situation:
I have column A and column B and I need an insert statement that will work in the following way:
B(0) = A(0)
B(X) = B(X-1)+A(X)

Can someone help?

Thanks in advance.

Comments

  • ushitaki
    ushitaki Member Posts: 1,128
    edited July 2007
    What is X?

    ----

    Ah, is this your homework's requirement?
    By the way, there is no need using table on RDBMS.
    SQL> create table Fibonacci 
      2  (x int,
      3   a int,
      4   b int)
      5  ;
    Table created.
    
    SQL> insert into Fibonacci values (0,1,1);
    1 row created.
    
    SQL> insert into Fibonacci values (1,2,1);
    1 row created.
    
    SQL> declare
      2    max_x int := 9; 
      3  begin
      4    for i in 1..max_x loop
      5      insert into Fibonacci 
      6        select i+1,a+b,a from Fibonacci where x=i;
      7    end loop;
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from Fibonacci;
    
             X          A          B
    ---------- ---------- ----------
             0          1          1
             1          2          1
             2          3          2
             3          5          3
             4          8          5
             5         13          8
             6         21         13
             7         34         21
             8         55         34
             9         89         55
            10        144         89
    
    11 rows selected.
    ----

    Message was edited by:
    ushitaki
  • Kum K
    Kum K Member Posts: 1,697 Bronze Badge
    Does x--> rownumber?
  • 586231
    586231 Member Posts: 4
    Yes, X is the rownumber
  • Kum K
    Kum K Member Posts: 1,697 Bronze Badge
    This should be the answer.
    I don't think its possible using a single insert statement unless somebody comes with a great idea.
  • 586231
    586231 Member Posts: 4
    Yeah, that covers it up quite nicely, with few modifications.

    Thanks a lot.
  • ushitaki
    ushitaki Member Posts: 1,128
    edited July 2007
    There is not 0 on rownumber, rownuber is origined from 1.

    By the way...
    For your homework, please refer [url http://en.wikipedia.org/wiki/Fibonacci_number]Fibonacci number, and program with PL/SQL or something by yourself.
    Maybe, you get more good performance.
  • 572471
    572471 Member Posts: 984 Green Ribbon
    if you want to calculate Fibonacci numbers - it can easily be done with model clause:
    SQL> select * from dual
      2   model
      3    dimension by (0 x)
      4    measures (0 A)
      5     rules(A[for x from 1 to 15 increment 1]=A[CV()-1]+nvl(A[CV()-2],1))
      6  /
    
             X          A
    ---------- ----------
             0          0
             1          1
             2          1
             3          2
             4          3
             5          5
             6          8
             7         13
             8         21
             9         34
            10         55
            11         89
            12        144
            13        233
            14        377
            15        610
    
    16 rows selected
    
    SQL> 
    User_ZUUOD
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    if you want to calculate Fibonacci numbers - it can easily be done with model clause:
    Or with XQuery (though really slow):
    michaels>  select rownum - 1 r, column_value fib 
       from XMLTable ('declare function local:fib ($i)
                       {
                         if ($i = 0) then (0)
                         else if ($i = 1) then (1)
                         else if($i >= 2) then number(local:fib($i - 1) + local:fib($i - 2))
                         else (1)
                       }; (: eof :)            
                       for $i in 0 to 15
                       return local:fib($i)')
    
             R FIB  
    ---------- -----
             0 0    
             1 1    
             2 1    
             3 2    
             4 3    
             5 5    
             6 8    
             7 13   
             8 21   
             9 34   
            10 55   
            11 89   
            12 144  
            13 233  
            14 377  
            15 610  
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    btw.

    there's even a math formula for generating fibonacci numbers quite elegantly:
    michaels>  select level - 1 r,
           round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
          from dual
    connect by level <= 16
    
             R        FIB
    ---------- ----------
             0          0
             1          1
             2          1
             3          2
             4          3
             5          5
             6          8
             7         13
             8         21
             9         34
            10         55
            11         89
            12        144
            13        233
            14        377
            15        610
  • 586231
    586231 Member Posts: 4
    Actually the problem is a bit more complicated that generating fibonacci numbers:

    I have the following table structure:
    ID TRANSACTIONS TOTAL_TRANSACTIONS_ACC
    ------------------------------------------
    1 | 100 | 100
    1 | 120 | 220
    1 | 50 | 270
    2 | 80 | 80
    2 | 160 | 240
    2 | 20 | 260
    3 | 75 | 75
    3 | 100 | 175
    3 | 50 | 225

    So I need to populate the TOTAL_TRANSACTIONS_ACC column with information from the TRANSACTIONS column in regards to the ID column

    Any ideas?
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Right, nothing to do with fibonacci ;-)
    Actually the problem is a bit more complicated that generating fibonacci numbers
    Actually it is simpler now:
    michaels>  with t as (
      select 1 id, 100 transactions from dual union all
      select 1 ,   120              from dual union all
      select 1 ,   50               from dual union all
      select 2 ,   80               from dual union all
      select 2 ,   160              from dual union all
      select 2 ,   20               from dual union all
      select 3 ,   75               from dual union all
      select 3 ,   100              from dual union all
      select 3 ,   50               from dual)
    select t.*, sum(transactions) over (partition by id order by rownum /* choose appropriate in your real scenario */) total_transactions_acc from t
    
            ID TRANSACTIONS TOTAL_TRANSACTIONS_ACC
    ---------- ------------ ----------------------
             1          100                    100
             1          120                    220
             1           50                    270
             2           80                     80
             2          160                    240
             2           20                    260
             3           75                     75
             3          100                    175
             3           50                    225
This discussion has been closed.