DB2: How do I display all dates in a range

Question

I want to expand a range using DB2(on iSeries) query. For example, I have the following value in a table

2016-10-01 2016-10-03 600

I want the output as

2016-10-01 200 
2016-10-02 200
2016-10-03 200

I tried but I am not able to develop the query. It should be somewhere in similar lines as below.

---

Table (MYTABLE) has two columns. Below is snapshot

START_DT    END_DT    
2016-01-01  2016-01-03

On this query

with temp1 as                                 
(                                             
  SELECT start_dt, end_dt, start_dt as dt     
  FROM mytable                                
    UNION                                     

  SELECT start_dt, end_dt, dt + 1 day as dt   
  FROM temp1                                  
  WHERE dt < end_dt                           
)                                             
SELECT dt                                     
FROM temp1    

I am getting the error "Column list not valid for table".

I tried this as well

with temp1 (start_dt, end_dt, dt) as             
(                                                
  SELECT start_dt, end_dt, start_dt as dt        
  FROM mytable                                   
    UNION                                        

  SELECT start_dt, end_dt, dt + 1 day as dt      
  FROM temp1                                     
  WHERE dt < end_dt                              
)                                                
SELECT dt                                        
FROM temp1  

This is throwing error "Keyword not allowed in recursive common table expression TEMP1."

Answer 1

I did a test -- this works on 9.7

with table1(start_dt,end_dt, amount) as
(
  values (timestamp('2017-01-01'), timestamp('2017-01-03'), 600)

), this_is_not_a_reserved_word (start_dt, end_dt, d, amount) as
(
  SELECT start_dt, end_dt, start_dt as d,
         amount/ (timestampdiff(16,end_dt-start_dt)+1) as amount
  FROM table1
--  WHERE tab_id_id = 518621     

    UNION ALL

  SELECT start_dt, end_dt, d + 1 day , amount
  FROM this_is_not_a_reserved_word
  WHERE d < end_dt
)
SELECT d, amount
FROM this_is_not_a_reserved_word

---

original answer

Here you go:

with this_is_not_a_reserved_word as
(
  SELECT start_dt, end_dt, start_dt as dt, amount/timestampdiff(16,start_dt-end_dt) as amount
  FROM table1
  WHERE tab_id_id = 518621     

    UNION 

  SELECT start_dt, end_dt, dt + 1 day as dt, amount
  FROM this_is_not_a_reserved_word
  WHERE dt < end_dt
)
SELECT dt, amount
FROM this_is_not_a_reserved_word

If start_dt and end_dt are type date and not timestamp use:

amount/timestampdiff(16,timestamp(start_dt)-timestamp(end_dt)) as amount

Answer 2

try this

with temp1  ( start_dt,   end_dt, DateCalc, num)  as                             
(                                             
  SELECT start_dt, end_dt, start_dt, 0  
  FROM  yourtable                                
  UNION  all                                   
  SELECT start_dt, end_dt,  DateCalc+ 1 day, num +1
  FROM temp1                                  
  WHERE DateCalc < end_dt                           
)                                             
SELECT DateCalc                                     
FROM temp1