## Forum Stats

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

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Insert problem

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

• 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
• Yes, X is the rownumber
• This should be the answer.
I don't think its possible using a single insert statement unless somebody comes with a great idea.
• Yeah, that covers it up quite nicely, with few modifications.

Thanks a lot.
• 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.
• 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> ```
• 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  ```
• 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```
• 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?
• 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.