How can I implement recursion in an Oracle SQL Select Script

Question

I am trying to create a table in Oracle SQL by querying another table which was not constracted well. I have the following data in my original table for example:

Deal No.        Start Date      End Date     Value
1256            20.01.22        25.01.22     10,000 
1489            23.01.22        27.01.22      5,000

I need to create a table using a select statement with a value for each day for each deal so that the results look like this:

Deal No.      Date         Value
1256          20.01.22     10,000
1256          21.01.22     10,000
1256          22.01.22     10,000
1256          23.01.22     10,000
1489          23.01.22      5,000
1489          24.01.22      5,000
1489          25.01.22      5,000
1489          26.01.22      5,000
1489          27.01.22      5,000

This table is now much easier to query for the end user.

Any assistance much appreciated.

Regards

Herbz

when it comes to performance, the original design, woul be much faster to query over. What is the information you like to archive from the original Table? Why does your 10k Value ends by 23.01.22 is it a failure or does it make sense to some background? — fisi-pjm, Aug 22 '22 at 14:45
Sounds like the problem is ease of use for the end user. What exactly do they need to do that is difficult with the original table design, which - as previously noted - is *far* better for performance and most use cases. Why de-normalize? — pmdba, Aug 22 '22 at 14:49
The table needs to be denormalised so that it can be joined to other existing tables which are in a denormalised state — Talch, Aug 22 '22 at 14:54
Why rows for ID 1256 and dates between 23.01 and 25.01 are missing? — astentx, Aug 22 '22 at 15:05
You could generate the rows as you join; or if you really want the data in this form separately, create a view (maybe materialized) rather than a table that will have to be maintained as data changes in the existing table? — Alex Poole, Aug 22 '22 at 15:09

score 0 · Answer 1 · answered Aug 22 '22 at 15:16

Try

WITH t_dates_between(id, startdate, enddate, value) AS (
  SELECT id, startdate, enddate, value 
    FROM t_dates
  UNION ALL
  SELECT id, startdate+1, enddate, value 
    FROM t_dates_between
   WHERE startdate < enddate
)
SELECT id, startdate, value 
  FROM t_dates_between
 ORDER BY id, startdate;

Note: the value-column is missing in your fiddle.

How can I implement recursion in an Oracle SQL Select Script

1 Answers1