connect by

royjayd
royjayd used Ask the Experts™
on
hi

I am trying to understand what 'connect by' is.


CREATE TABLE t (
rid NUMBER(12),
col1 VARCHAR2(30),
col2 VARCHAR2(300));

CREATE SEQUENCE seq_t_rid;

INSERT INTO t
SELECT seq_t_rid.NEXTVAL, dbms_crypto.randombytes(15), dbms_crypto.randombytes(150)
FROM DUAL
CONNECT BY LEVEL <= 1000;  -- what does CONNECT BY do ?

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>CONNECT BY LEVEL <= 1000;  -- what does CONNECT BY do ?


Most of the time it is for hierarchical queries.

In this case, LEVEL is a pseudo column that goes with CONNET BY.  It is generating a result set of 1000 rows.
awking00Information Technology Specialist

Commented:
SQL> select level from dual
  2  connect by level <= 10;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
Information Technology Specialist
Commented:
A little better idea (sorry I don't have privileges on dbms_crypto)
SQL> create sequence sequence_t_rid start with 1001;
Sequence created.
SQL> select level, sequence_t_rid.nextval from dual
  2  connect by level <= 10;

     LEVEL    NEXTVAL
---------- ----------
         1       1001
         2       1002
         3       1003
         4       1004
         5       1005
         6       1006
         7       1007
         8       1008
         9       1009
        10       1010

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial