Re: Hierarchical Queries as views?

Hi.

A colleague who worked with a hierarchical query in a view, which was joined to another table told me that the join could destroy the ordering of the hierarchical query, just cause the view is used as a list of values for the joining table. However, I didnt check this on my own.
But to be sure that the ordering is not destroyed I would try the following:

Extend the view with a aliased rownum-column:

 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, ROWNUM as ROW_NUMBER  from org_map
 connect by prior loc_id = mgr_loc_id
 start with mgr_loc_id is null;

Then order the whole query by this column:

 select *
   from org_map_view omv, any_other_table aot   where omv.joining_column = aot.joining_column   order by omv.row_number;

HTH Greez,
Angelo.

Andre Allen wrote:
>
> 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. |
 

-- 
-----------------------------------------------------------------
Angelo Cavallaro     /  /  /   \     TECTUM Beratungsgesellschaft
ORACLE-SW-Berater   /  /  /     \    für Informationsverarbeitung
ancavall.NOSPAM    /  /  /-------\   Tel: 0711/99073-10
@aol.com          /__/__/_________\  Fax: 0711/99073-99

-----------------------------------------------------------------
	 Visit the Home of the Green Ribbon Campaign:

		http://www.ponce.oisoft.com/

-----------------------------------------------------------------