Link to home
Start Free TrialLog in
Avatar of uz3a
uz3a

asked on

Connect By Prior

Hi,

I am trying to use connect by prior in hierarchical query. I am able to =
view all parents with their children in a certain format but I am also =
getting all the records one more time and couldn't figure why. The =
statement I am using is

 SELECT ID
 ,LPAD('-',2*(LEVEL-1)) ||OPT_DESC AS OPT_DESC
 , PARENT_ID,LEVEL
 FROM SCS_MENU_OPTION
 WHERE ISSHOWN =3D 1
    CONNECT BY PRIOR ID =3D PARENT_ID

If I used start with, I get only one sub child record and its sub =
children. The information in the table can be illustrated as below:

--parent 1
      -- child 1
      --child 2
            -- child 1
--parent 2
      -- child 1
            -- child 1
            -- child 2
      -- child 2
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of uz3a
uz3a

ASKER

Thank you! I tried start with but PARENT_ID = NULL. Obviously I wasn't thinking clear.