How to query all the dates between 2023-07-03 and 2023-08-06 (5 weeks from the start date)?

Question

Below database created:

`INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('COMMUNITY', 
TO_DATE('2023-07-03', 'YYYY-MM-DD'), 5);
INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('HAF', TO_DATE('2023-07- 
03', 'YYYY-MM-DD'), 4);
INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('HUBB', TO_DATE('2023- 
07-03', 'YYYY-MM-DD'), 4);

Query tried:

`select BR_START + level - 1 DT
 from   t4s_branch_data where BR_NAME = 'COMMUNITY'
 connect by level <= (
 (BR_START + BR_COURSE_LN*7-1) - BR_START + 1);

However, this prints 5,000 rows of repeated data

Answer 1

you need to limit the rows you want to use BEFORE you apply the connect by clause:

with comm as
  (select * from t4s_branch_data where BR_NAME = 'COMMUNITY')
select BR_START + level - 1 DT
 from  comm
 connect by level <= (
 (BR_START + BR_COURSE_LN*7-1) - BR_START + 1);

or use an inline view instead of a WITH clause

Answer 2

One of the options is to have (more than enough) Levels in joined subquery limiting the number of dates generated in Where clause:

SELECT        t.BR_START + l.LVL "DT"
FROM          t4s_branch_data t
INNER JOIN    ( SELECT LEVEL - 1 "LVL" From Dual Connect By LEVEL <= 60 ) l ON(1 = 1)
WHERE         t.BR_NAME = 'COMMUNITY' And l.LVL <= ( (t.BR_START + t.BR_COURSE_LN*7-1) - t.BR_START )