1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

start with connect by prior

Discussion in 'SQL PL/SQL' started by senthil_m01, Sep 25, 2010.

  1. senthil_m01

    senthil_m01 Guest

    Hi,

    We have a weird problem.

    we have a table in oracle 8i called "test" and when i try to query the said table using a select statement given below,

    select *
    from test
    start with parent is null
    connect by prior child = parent;

    we get the result set just fine.

    We are migrating from oracle 8i to 10g.

    We created the same table test in 10g and tried to query the table using the same select statement.

    we got the result set just fine.

    Now, the problem here is the order in which the result set is selected from the table varies.

    For examples sake,i will make it simple, lets say there are 3 rows in the table.

    In 8i the result set goes in the order,

    /COLUMN_NAME/
    ---------------
    /ROW 1/
    /ROW 2/
    /ROW 3/


    but in 10g the result set goes in the order,

    /COLUMN_NAME/
    ---------------
    /ROW 2/
    /ROW 3/
    /ROW 1/

    for better understanding i limited the example to 3 rows. There are 1507 rows in the table.

    We need oracle 10g to select the result set in the same order as it does in 8i since we are fetching the rows and performing
    a certain function in c++ which demands the sequence of the data should be strictly the same as in 8i.

    QUestions:
    1.what does "start with connect by prior" exactly does?
    2.Is this problem because of the version difference?
    3.What is the solution?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    1. This connects the current record to its parent record.
    2. Possibly. I can run the following query against the EMP table in 8.1.7.4 and 10.2.0.4 and return the same results because I explicitly order the results:

    Code (SQL):
     
    SQL> SELECT lpad(' ', 2*(level -1))||D.DEPTNO||' - '||D.DNAME DEPARTMENT, E.ename, D.DEPTNO
    2 FROM EMP E, DEPT D
    3 WHERE E.DEPTNO = D.DEPTNO
    4 CONNECT BY e.mgr = prior E.empno
    5 START WITH e.mgr IS NULL
    6 ORDER BY e.deptno, level
    7 /
     
    DEPARTMENT ENAME DEPTNO
    ------------------------- ---------- ----------
    10 - ACCOUNTING KING 10
    10 - ACCOUNTING CLARK 10
    10 - ACCOUNTING MILLER 10
    20 - RESEARCH JONES 20
    20 - RESEARCH SCOTT 20
    20 - RESEARCH FORD 20
    20 - RESEARCH ADAMS 20
    20 - RESEARCH SMITH 20
    30 - SALES BLAKE 30
    30 - SALES MARTIN 30
    30 - SALES TURNER 30
    DEPARTMENT ENAME DEPTNO
    ------------------------- ---------- ----------
    30 - SALES JAMES 30
    30 - SALES ALLEN 30
    30 - SALES WARD 30
     
    14 ROWS selected.
     
    SQL>
     
     
    Never rely on any default behaviour you may see in an Oracle database with respect to data order as Oracle cannot guarantee that won't change from release to release.
     
    nestor likes this.