Home » SQL & PL/SQL » SQL & PL/SQL » query Hierarchical data
query Hierarchical data [message #280838] Thu, 15 November 2007 00:05 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
for querying Hierarchical data oracle suggest start with connect by .
First I am confused with word prior what it does ?
second
this is the query i am using to query Hierarchical data

SELECT OBJECT_TYPE_REVIEW_ID , PARENT_OBJECT_TYPE_REVIEW_ID ,LEVEL
FROM ND_RM_DEPENDENCIES
START WITH OBJECT_TYPE_REVIEW_ID = 707
connect by PRIOR PARENT_OBJECT_TYPE_REVIEW_ID= OBJECT_TYPE_REVIEW_ID

this query works , but my concern is the value for
START WITH OBJECT_TYPE_REVIEW_ID = 707

I have to pass many values and not one, something like
START WITH OBJECT_TYPE_REVIEW_ID in (707,863)
so how to modify the query to use "in" instead of "=" . The problem is for 704 i get list of rows for example

object_type_review_id parent_object_type_review_id level
707 863 1
863 705 2
........................................................
........................................................
........................................................

822 704 3


suppose I get two OBJECT_TYPE_REVIEW_ID S (707, 863) and If I modifiy query to use in (707,863) then the resultset contains all the rows starting from 707 to last (this contains 863) and all the rows starting from 863 to last which is duplicate information how to resolve this ?

Attached is my data returned by query



  • Attachment: data.GIF
    (Size: 27.25KB, Downloaded 102 times)
Re: query Hierarchical data [message #280846 is a reply to message #280838] Thu, 15 November 2007 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

First I am confused with word prior what it does ?

It refers to the previous row in the hierarchy, that is it defined the hierarchy dependency.

Quote:

which is duplicate information how to resolve this ?

This is not duplicate, this is part of second tree you asked.
See level is not the same.

Regards
Michel

Re: query Hierarchical data [message #280848 is a reply to message #280838] Thu, 15 November 2007 00:45 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the Oracle documentation first, including the Examples.

If you cannot understand those examples, describe exactly what is not clear using Oracle's examples.

Ross Leishman
Previous Topic: Help with joins
Next Topic: DML statements notification
Goto Forum:
  


Current Time: Fri Dec 09 09:58:50 CST 2016

Total time taken to generate the page: 0.12652 seconds