Hierarchical Queries as views?

From: Andre Allen <andre_at_interaccess.com>
Date: 1998/03/04
Message-ID: <6dkoh9$248_002@nntp.interaccess.com>#1/1

To anyone with experience using hierarchical queries in Oracle

I've created little view, using a hierarchical query approach.

create or replace view org_map_view as
select id as org_map_id, org_id, level as hier_level, loc_id, mgr_loc_id, map_type
from org_map
connect by prior loc_id = mgr_loc_id
start with mgr_loc_id is null

I am attempting to get around some of Oracle's rules in regards to hierarchical queries. (i.e. CONNECT BY cannot contain a join). With the above approach, I am able to join the results of this view to another table.

My question is, since Oracle maintains that the results of hierarchical queries are always returned in parent-child-grandchild order, is this rule now broken with this view per the "no explicit order without an order-by" rule?

While the rows return in the correct order now, I want to make sure that it is not a fluke because of the lack of real (and/or) more abundant data. My theory on the CONNECT BY rule is that since the view is a stored SQL statement, the join takes place after the initial query. As far as the order goes, I have no clue.

Any opinions or further insight would be greatly appreciated.

  .:'`':..:'`':.. Andre T. Allen ..:'`':..:'`':.   |  Disclaimer:  |
 |             andre_at_interaccess.com            |  | All opinions  |
|  Systems Admin/Oracle 7 DBA/MS-SQL Server DBA  | | expressed are | 
+----------  Reid Psychological Systems  --------+ | my own.       |

Received on Wed Mar 04 1998 - 00:00:00 CST