Duplicate rows using Connect by level

Question

I wish to duplicate rows based on a column value using CONNECT BY LEVEL.

This is the code I used:

SELECT 
    tbl1.*,
    (tbl1.START_WEEK - tbl1.END_WEEK) wks_inbtwn

FROM
    My_SQL_table tbl1 INNER JOIN (SELECT rownum repeat FROM dual CONNECT BY LEVEL <= tbl1.END_WEEK ) tbl2
    ON tbl2.Repeat > tbl1.START_WEEK

I keep getting the error:

SQL Error [904] [42000]: ORA-00904: "tbl1"."START_WEEK": invalid identifier

My table is as below:

+------------+------------+----------+
|  Site_NUM  | start_week | end_week |
+------------+------------+----------+
| France     |         50 |       52 |
| Germany    |         41 |       43 |
| USA        |         12 |       13 |
+------------+------------+----------+

The result I want is as below:

+----------+---------+
| Site_NUM | Week_no | 
+----------+---------+
| France   |      51 |
| France   |      52 |
| Germany  |      42 |
| Germany  |      43 |
| USA      |      13 |
+----------+---------+

Any help would be greatly appreciated, Thanks in advance.

Answer 1

Oracle Setup:

CREATE TABLE My_SQL_table ( Site_NUM, start_week, end_week ) AS
SELECT 'France',  50, 52 FROM DUAL UNION ALL
SELECT 'Germany', 41, 43 FROM DUAL UNION ALL
SELECT 'USA',     12, 13 FROM DUAL;

Query: Using CONNECT BY

SELECT site_num,
       COLUMN_VALUE wks_inbtwn
FROM   My_SQL_table tbl1
       CROSS JOIN 
       TABLE(
         CAST(
           MULTISET(
             SELECT tbl1.START_WEEK + LEVEL
             FROM   DUAL
             CONNECT BY tbl1.START_WEEK + LEVEL <= tbl1.END_WEEK
           )
           AS SYS.ODCINUMBERLIST
         )
       )

Output:

SITE_NUM | WKS_INBTWN
:------- | ---------:
France   |         51
France   |         52
Germany  |         42
Germany  |         43
USA      |         13

Query 2: Using a recursive sub-query factoring clause

WITH rsqfc ( site_num, start_week, end_week ) AS (
  SELECT site_num, start_week + 1, end_week
  FROM   my_sql_table
UNION ALL
  SELECT site_num, start_week + 1, end_week
  FROM   rsqfc
  WHERE  start_week < end_week
)
SELECT site_num, start_week AS wks_inbtwn
FROM   rsqfc
ORDER BY site_num, wks_inbtwn

Output:

SITE_NUM | WKS_INBTWN
:------- | ---------:
France   |         51
France   |         52
Germany  |         42
Germany  |         43
USA      |         13

db<>fiddle here