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.

A good way to debug invalid identifier errors is to start taking out the references, one by one, and rerunning the query until the error goes away. Taking out the references may not give you the data you desire but it will point you to the source of the error. — Sam M, May 23 '19 at 01:25
It will also be very helpful if you include the exact DDL statement you used to create My_SQL_table. In a `create table` statement there are ways to make Oracle column names case-sensitive. Seeing your DDL statement will help eliminate that as a possible cause. — Sam M, May 23 '19 at 01:27
Please refer this [answer](https://stackoverflow.com/a/6030439/2451726), you may get some idea about the case-sensitive on the column names — Arulkumar, May 23 '19 at 03:16

score 1 · Accepted Answer · answered May 23 '19 at 09:11

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

Duplicate rows using Connect by level

1 Answers1