ghostrider ghostrider - 1 year ago 40
SQL Question

"OR" operator in CONNECT BY PRIOR

The rows in the database are:

DATAID OWNERID PARENTID
111 123 133
976 346 111
987 976 657


I want to display all these rows but by using the following query only the top 2 rows are getting displayed and I need all the 3 rows:

SELECT * FROM DTREE start with DATAID=111
connect by prior dataid=parentid OR dataid=ownerid;


It seems it is running only the dataid=parentid part and not the second one. Can someone please help to find the issue.

Answer Source

You are missing the PRIOR operator applying to the second occurrence of DATAID:

SELECT * FROM DTREE  start with DATAID=111
connect by prior dataid=parentid OR   prior  /* <-- MISSING!!! */  dataid=ownerid;

Alternatively:

connect by prior dataid in (parented, ownerid);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download