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."
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