Integer Series Generators

CONNECT BY LEVEL Method

This tutorial demonstrates how to generate a series of integers using a novel application of the CONNECT BY clause first posted by Mikito Harakiri at Ask Tom "how to display selective record twice in the query?". Other techniques are discussed in the tutorials listed in the menu to the left.

With this technique you can generate a series of integers starting at "1" using a query like this.

select  level
from    dual
connect by level <= 10 ;
 
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 

Queries Without PRIOR

The query above is a special case of a more general type of query, those that do not use the PRIOR operator. Applying the technique to a table with two rows, "a" and "b", yields some insight into how such queries work.

break on level duplicates skip 1

column path format a10

select     level, sys_connect_by_path( key, '/' ) as path, key
from       t4
connect by level <= 3
order by   level, path ;
 
     LEVEL PATH       KEY
---------- ---------- ---
         1 /a         a
         1 /b         b

         2 /a/a       a
         2 /a/b       b
         2 /b/a       a
         2 /b/b       b

         3 /a/a/a     a
         3 /a/a/b     b
         3 /a/b/a     a
         3 /a/b/b     b
         3 /b/a/a     a
         3 /b/a/b     b
         3 /b/b/a     a
         3 /b/b/b     b
 

Without a CONNECT BY condition that uses PRIOR it appears Oracle returns all possible hierarchy permutations. This effect may be useful where an exponentially increasing number of output rows is required.

The effect also proves useful in situations where more than one integer series is required from a single query. See Multiple Integer Series: CONNECT BY LEVEL Method for more details and an example.

Note there is some debate about whether queries without PRIOR in the CONNECT BY clause are legal or not. This is discussed further in the "Gotchas" section below.

Variables

The original syntax for this technique works fine when the number of rows is hardcoded to a value greater than or equal to 1. If the number of rows is set with a bind variable whose value can be 0, negative, or null however, the technique may not work as expected. It always generates at least one row in these cases.

clear breaks

variable v_total_rows number

execute :v_total_rows := 0

select  level
from    dual
connect by level <= :v_total_rows ;
 
     LEVEL
----------
         1
 
execute :v_total_rows := -5

PL/SQL procedure successfully completed.

/

     LEVEL
----------
         1

1 row selected.


execute :v_total_rows := null

PL/SQL procedure successfully completed.

/

     LEVEL
----------
         1

1 row selected.

 

A simple WHERE clause fixes this behaviour.

execute :v_total_rows := 0

PL/SQL procedure successfully completed.


select  level
from    dual
WHERE   :V_TOTAL_ROWS >= 1
connect by level <= :v_total_rows ;

no rows selected

 
execute :v_total_rows := -5

PL/SQL procedure successfully completed.

/

no rows selected


execute :v_total_rows := null

PL/SQL procedure successfully completed.

/

no rows selected


execute :v_total_rows := 3

PL/SQL procedure successfully completed.

/

     LEVEL
----------
         1
         2
         3

3 rows selected.

 

Day of the Week Case Study

In the next snippet we apply the technique to the day of the week scenario we examined in prior tutorials.

select
  day_of_week ,
  t.val
from
  ( select level - 1 as day_of_week
    from dual
    connect by level <= 7
  ) i
    left outer join t using( day_of_week )
order by
  day_of_week
;
 
DAY_OF_WEEK        VAL
----------- ----------
          0
          1        100
          2
          3        300
          4        400
          5        500
          6
 

Gotchas

To Use PRIOR or Not to Use PRIOR, That is the Question

Laurent Schneider argues in his blog post Bible of Oracle that a clause like CONNECT BY LEVEL <= 10 is an illegal construct since it has no expressions qualified with the PRIOR operator, as dictated by this statement in the SQL Reference Manual

"in a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row." -- Oracle® Database SQL Reference 10g Release 2 (10.2)

Others argue that this statement is a documentation bug. The fact the CONNECT BY clause works without error in Oracle 10g and some 9i versions somewhat supports this view.

Note that the following queries, seemingly equivalent to the CONNECT BY LEVEL <= 10 solution, do not produce the desired 10 rows of output (as tested in Oracle 10g).

select  level
from    dual
connect by
  level <= 10
  AND PRIOR DUMMY = DUMMY
;
ERROR:
ORA-01436: CONNECT BY loop in user data


 
select  level
from    dual
connect by
  level <= 10
  AND PRIOR 1 = 1
;
ERROR:
ORA-01436: CONNECT BY loop in user data


 

The following variation may be more legal than the original solution since it includes a PRIOR condition and does not produce a CONNECT BY loop, but the PL/SQL call it contains makes it perform worse (from Re: Creating N Copies of a Row using "CONNECT BY CONNECT_BY_ROOT" - Volder).

select level
from   dual
connect by
  level <= 10 and
  PRIOR DBMS_RANDOM.VALUE IS NOT NULL
;
 
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 

The jury is still out on using CONNECT BY LEVEL to generate integers. Until there is a definitive answer, be aware there is a risk the technique may not work in future versions.

Issues

Issues with this technique, or variations of it, have been reported in Oracle versions earlier than 10.2. I have not tested these myself but here are some posts that describe problems.

In Oracle 9i, if you try the CONNECT BY LEVEL technique and get a single row when expecting muliple rows, like this

select level from dual connect by level < 10 ;
 
     LEVEL
----------
         1
 

putting the query in an inline view, as in this snippet, may help (I have not tested this).

select *
from   (select level from dual connect by level < 10) ;
 
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.
 

Acknowledgements

Mikito Harakiri, Tom Kyte, Laurent Schneider and other posters at these threads.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11821.html]SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11821.html">SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method
  • URL (href): http://www.sqlsnippets.com/en/topic-11821.html

Revision Notes

Date Category Note
2007-07-29 Updated Tutorial This tutorial has been updated to include a discussion of how hierarchical queries that do not have a CONNECT BY condition with PRIOR behave. A sample query that uses "PRIOR DBMS_RANDOM.VALUE IS NOT NULL" to make the CONNECT BY LEVEL technique conform to documented requirements has also been added.
2007-03-27 Updated Tutorial This tutorial has been updated to show how queries like select level from dual connect by level <= :v_total_rows can produce unexpected results when v_total_rows is 0, negative, or NULL. A simple WHERE clause is presented that fixes this behaviour.
2007-03-31 Revision Added "CONNECT BY Generator Rules | Ask Mr. Ed" link.