CODEWITHSUNDEEP
db2
Volcano
Volcano

Reputation: 1

Autoincrement in query

I need to create a query which increment value of current row by 8% to previous row.

Table (let's name it money) contains one row (and two columns), and it looks like

AMOUNT  ID
100.00  AAA

I just need to populate a data from this table like this way (one select from this table, eg. 6 iterations):

100.00 AAA
108.00 AAA
116.64 AAA
125.97 AAA
136.04 AAA
146.93 AAA

Upvotes: 0

Views: 80

Answers (1)

kkuduk
kkuduk

Reputation: 601

You can do that with a common table expression.

E.g. if your source looks like this:

db2 "create table money(amount decimal(31,2), id varchar(10))" 
db2 "insert into money values (100,'AAA')"

You can create the input data with the following query (I will include counter column for clarity):

db2 "with 
        cte(c1,c2,counter) 
     as 
        (select 
            amount, id, 1 
         from 
            money
         union all
         select 
            c1*1.08, c2, counter+1 
         from 
            cte 
         where counter < 10)
      select * from cte"

C1                                C2         COUNTER    
--------------------------------- ---------- -----------
                           100.00 AAA                  1
                           108.00 AAA                  2
                           116.64 AAA                  3
                           125.97 AAA                  4
                           136.04 AAA                  5
                           146.92 AAA                  6
                           158.67 AAA                  7
                           171.36 AAA                  8
                           185.06 AAA                  9
                           199.86 AAA                 10

To populate the existing table without repeating the existing row you use e.g. an insert like this:

$ db2 "insert into money 
     with 
        cte(c1,c2,counter) 
     as 
        (select 
            amount*1.08, id, 1 
         from 
            money
         union all
         select 
            c1*1.08, c2, counter+1 
         from 
            cte 
         where counter < 10) select c1,c2 from cte"

$ db2 "select * from money"

AMOUNT                            ID        
--------------------------------- ----------
                           100.00 AAA       
                           108.00 AAA       
                           116.64 AAA       
                           125.97 AAA       
                           136.04 AAA       
                           146.93 AAA       
                           158.68 AAA       
                           171.38 AAA       
                           185.09 AAA       
                           199.90 AAA       
                           215.89 AAA       

  11 record(s) selected.

Upvotes: 3

Related Questions