Oracle SQL: CONNECT BY LEVEL returning many rows

Question

I am having a first go at the CONNECT BY command, and I understand its potential to create loops with it. I was trying to create a query that generates a row for each timestamp between a starttime and an endtime, with a variable interval. When I run this query for each 'route' individually, it works perfectly. But when I try to run it for both routes at the same time, the loop keeps going.

What am I doing wrong?

SELECT ROUTE_NAME, START_TIME + (LEVEL - 1) * TIME_PERIOD OUTPUT_MOMENT
  FROM (SELECT *
          FROM (SELECT 1 / 24 AS TIME_PERIOD,
                       SYSDATE - 8 / 24 AS START_TIME,
                       SYSDATE + 3 / 24 AS END_TIME,
                       'ROUTE A' ROUTE_NAME
                  FROM DUAL
                UNION ALL
                SELECT 1 / 48 AS TIME_PERIOD,
                       SYSDATE - 8 / 24 AS START_TIME,
                       SYSDATE + 3 / 24 AS END_TIME,
                       'ROUTE B' ROUTE_NAME
                  FROM DUAL)
         WHERE ROUTE_NAME IN ('ROUTE A')
         --WHERE ROUTE_NAME IN ('ROUTE B')
         --WHERE ROUTE_NAME IN ('ROUTE A', 'ROUTE B')
       )
CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD

Result on Route A:

* ROUTE_NAME    OUTPUT_MOMENT
* ROUTE A   9/3/2018 5:41:01
* ROUTE A   9/3/2018 6:41:01
* ROUTE A   9/3/2018 7:41:01
* ROUTE A   9/3/2018 8:41:01
* ROUTE A   9/3/2018 9:41:01
* ROUTE A   9/3/2018 10:41:01
* ROUTE A   9/3/2018 11:41:01
* ROUTE A   9/3/2018 12:41:01
* ROUTE A   9/3/2018 13:41:01
* ROUTE A   9/3/2018 14:41:01
* ROUTE A   9/3/2018 15:41:01

Results on Route B:

* ROUTE_NAME    OUTPUT_MOMENT
* ROUTE B   9/3/2018 5:42:34
* ROUTE B   9/3/2018 6:12:34
* ROUTE B   9/3/2018 6:42:34
* ROUTE B   9/3/2018 7:12:34
* ROUTE B   9/3/2018 7:42:34
* ROUTE B   9/3/2018 8:12:34
* ROUTE B   9/3/2018 8:42:34
* ROUTE B   9/3/2018 9:12:34
* ROUTE B   9/3/2018 9:42:34
* ROUTE B   9/3/2018 10:12:34
* ROUTE B   9/3/2018 10:42:34
* ROUTE B   9/3/2018 11:12:34
* ROUTE B   9/3/2018 11:42:34
* ROUTE B   9/3/2018 12:12:34
* ROUTE B   9/3/2018 12:42:34
* ROUTE B   9/3/2018 13:12:34
* ROUTE B   9/3/2018 13:42:34
* ROUTE B   9/3/2018 14:12:34
* ROUTE B   9/3/2018 14:42:34
* ROUTE B   9/3/2018 15:12:34
* ROUTE B   9/3/2018 15:42:34
* ROUTE B   9/3/2018 16:12:34
* ROUTE B   9/3/2018 16:42:34

Results on both (More rows exist):

* ROUTE_NAME    OUTPUT_MOMENT
* ROUTE A   9/3/2018 5:43:21
* ROUTE A   9/3/2018 6:43:21
* ROUTE A   9/3/2018 7:43:21
* ROUTE A   9/3/2018 8:43:21
* ROUTE A   9/3/2018 9:43:21
* ROUTE A   9/3/2018 10:43:21
* ROUTE A   9/3/2018 11:43:21
* ROUTE A   9/3/2018 12:43:21
* ROUTE A   9/3/2018 13:43:21
* ROUTE A   9/3/2018 14:43:21
* ROUTE A   9/3/2018 15:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:13:21
* ROUTE A   9/3/2018 15:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 9:43:21
* ROUTE A   9/3/2018 14:43:21
* ROUTE A   9/3/2018 15:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21
* ROUTE B   9/3/2018 13:13:21
* ROUTE B   9/3/2018 13:43:21
* ROUTE B   9/3/2018 14:13:21
* ROUTE B   9/3/2018 14:43:21
* ROUTE B   9/3/2018 15:13:21
* ROUTE B   9/3/2018 15:43:21
* ROUTE B   9/3/2018 16:13:21
* ROUTE B   9/3/2018 16:43:21
* ROUTE B   9/3/2018 10:43:21
* ROUTE B   9/3/2018 11:13:21
* ROUTE B   9/3/2018 11:43:21
* ROUTE B   9/3/2018 12:13:21
* ROUTE B   9/3/2018 12:43:21

Alex Poole · Accepted Answer · 2018-03-09 13:11:56Z

The connect by is only based on the time, so you're connecting every time for route A with route B and vice versa.

The simple fix seems to be to make it:

CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
AND ROUTE_NAME = PRIOR ROUTE_NAME

to restrict it to the single route at a time; but that then forms a loop, so you need to add in a non-deterministc function call too to prevent that; for example:

CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
AND ROUTE_NAME = PRIOR ROUTE_NAME
AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL

which gets:

ROUTE_NAME OUTPUT_MOMENT      
---------- -------------------
ROUTE A    2018-03-09 05:00:08
ROUTE A    2018-03-09 06:00:08
ROUTE A    2018-03-09 07:00:08
ROUTE A    2018-03-09 08:00:08
ROUTE A    2018-03-09 09:00:08
ROUTE A    2018-03-09 10:00:08
ROUTE A    2018-03-09 11:00:08
ROUTE A    2018-03-09 12:00:08
ROUTE A    2018-03-09 13:00:08
ROUTE A    2018-03-09 14:00:08
ROUTE A    2018-03-09 15:00:08
ROUTE B    2018-03-09 05:00:08
ROUTE B    2018-03-09 05:30:08
ROUTE B    2018-03-09 06:00:08
ROUTE B    2018-03-09 06:30:08
ROUTE B    2018-03-09 07:00:08
ROUTE B    2018-03-09 07:30:08
ROUTE B    2018-03-09 08:00:08
ROUTE B    2018-03-09 08:30:08
ROUTE B    2018-03-09 09:00:08
ROUTE B    2018-03-09 09:30:08
ROUTE B    2018-03-09 10:00:08
ROUTE B    2018-03-09 10:30:08
ROUTE B    2018-03-09 11:00:08
ROUTE B    2018-03-09 11:30:08
ROUTE B    2018-03-09 12:00:08
ROUTE B    2018-03-09 12:30:08
ROUTE B    2018-03-09 13:00:08
ROUTE B    2018-03-09 13:30:08
ROUTE B    2018-03-09 14:00:08
ROUTE B    2018-03-09 14:30:08
ROUTE B    2018-03-09 15:00:08
ROUTE B    2018-03-09 15:30:08
ROUTE B    2018-03-09 16:00:08

34 rows selected.

You could also do two connect by queries and union the results together, possibly pulling the time range into a CTE to avoid duplicating that:

WITH START_END AS (
  SELECT SYSDATE - 8 / 24 AS START_TIME,
         SYSDATE + 3 / 24 AS END_TIME
  FROM DUAL
)
SELECT 'ROUTE A' ROUTE_NAME,
       START_TIME + (LEVEL - 1) / 24 AS OUTPUT_MOMENT
  FROM START_END
CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / (1 / 24)
UNION ALL
SELECT 'ROUTE B' ROUTE_NAME,
       START_TIME + (LEVEL - 1) / 48 AS OUTPUT_MOMENT
FROM START_END
CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / (1 / 48)

Using / ( 1 / 24) looks odd when you could instead do * 24, but you actually get a slightly different result because of rounding errors; with the latter you get an extra row for route A. You could rearrange the logic further to avoid that confusion though.

Yep, that's it! Thank you very much for that quick response. That gives me more functions to google :)

wolfrevokcats · Accepted Answer · 2018-03-09 13:43:36Z

2

What you get here is doubling the rows with every level of the hierarchical query.
Consider a simple example:

WITH dbl AS (
    SELECT * FROM dual  UNION ALL SELECT *FROM dual
)
SELECT *FROM dbl CONNECT BY LEVEL <= N

For N=2 the query returns 6 rows, for N=6 it returns 126 rows, for N=10 -- 2046 rows. So we see that the row number grows exponentially.

Your query works in a similar fashion. To fix this you can either move union all to the outer level, running separate hierarchical queries for each route:

SELECT ROUTE_NAME, START_TIME + (lvl - 1) * TIME_PERIOD
FROM (
    SELECT LEVEL AS lvl, ROUTE_NAME AS "ROUTE A"... 
    ...
    FROM DUAL CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
)
UNION ALL 
SELECT ROUTE_NAME, START_TIME + (lvl - 1) * TIME_PERIOD
FROM (
    SELECT LEVEL AS lvl, ROUTE_NAME AS "ROUTE B", ... 
    ...
    FROM DUAL CONNECT BY (LEVEL - 1) <= (END_TIME - START_TIME) / TIME_PERIOD
)

Or prevent one route from following the other with ROUTE_NAME = PRIOR ROUTE_NAME, as was suggested in another answer.

edited Mar 9, 2018 at 13:43

answered Mar 9, 2018 at 12:54

wolfrevokcats

2,1101 gold badge14 silver badges13 bronze badges

1 Comment

Ronald Over a year ago

Thank you for the explanation!

Collectives™ on Stack Overflow

Oracle SQL: CONNECT BY LEVEL returning many rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related