## Multiple Integer Series

### CONNECT BY LEVEL Method

With the CONNECT BY LEVEL approach there is also no need to use the Join Method. Multiple integer series can be created using a query like this one (the PATH column is included to illustrate how the query works)

```set null "(null)"

break on key duplicates skip 1

column path format a10

select
key, qty, level as integer_value, sys_connect_by_path( key, '/' ) as path
from
t3
where
qty >= 1
connect by
KEY = PRIOR KEY and
prior dbms_random.value is not null and
level <= t3.qty
order by
key, integer_value
;
```
```KEY        QTY INTEGER_VALUE PATH
--- ---------- ------------- ----------
c            1             1 /c

d            2             1 /d
d            2             2 /d/d

e            3             1 /e
e            3             2 /e/e
e            3             3 /e/e/e
```

or this approach (only possible on version 10g or greater)

```select
key, qty, level as integer_value, sys_connect_by_path( key, '/' ) as path
from
t3
where
qty >= 1
connect by
KEY = CONNECT_BY_ROOT KEY and
level <= t3.qty
order by
key, integer_value
;
```
```KEY        QTY INTEGER_VALUE PATH
--- ---------- ------------- ----------
c            1             1 /c

d            2             1 /d
d            2             2 /d/d

e            3             1 /e
e            3             2 /e/e
e            3             3 /e/e/e
```

Note these approaches will not work for rows where no integer series is required, like the rows with KEY in ( 'a', 'b' ).

#### Gotchas

The CONNECT_BY_ROOT technique may not work without error in all cases and it may not work in Oracle versions beyond 10g. This is because it violates two restrictions documented at Hierarchical Query Operators:

1. "In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator."
2. "You cannot specify (CONNECT_BY_ROOT) in the START WITH condition or the CONNECT BY condition."

The fact that the query above contradicts the documentation yet works without error in 10g suggests a bug in either the documentation or the SQL engine.

On my system the following variation of the CONNECT_BY_ROOT query raised some rather severe ORA errors casting further doubt on the technique's reliability (do not run this query on your own systems).

```select
key, qty, level as integer_value
from
t3
qty >= 1
connect by
KEY = CONNECT_BY_ROOT KEY and
level <= t3.qty
order by
key, integer_value
;
ERROR at line 1:
ORA-03113: end-of-file on communication channel

ERROR:
ORA-03114: not connected to ORACLE

From the .trc file:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
```

#### 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-12525.html]SQL Snippets: Multiple Integer Series - CONNECT BY LEVEL Method[/url]