Re: SQL-qwery with a ''connect by prior'' in alfabetic order?(hierachical tree)

You could use a "temporary" table, which you populate using insert into tablename(col1, col2, etc)
select statement (with connect by).

Then when you retrieve information from the table, you can specify which column to order by.

This is what we have done here, as it suits our circumstances.

sammy_6_at_my-deja.com wrote in message <86k6r8$94v$1_at_nnrp1.deja.com>...
>You could try adding an index mgr,ename and oracle should use the index
>and return the rows in the right order, you may have to change the top
>level of your structure (mgr is Null) so that the top level comes out
>in the right order. (Having the top level flagged by null is not a
>great idea anyway - full table scan etc.).
>
> Sammy_6
>
>
>In article <388cb36c$0$20554_at_reader3.casema.net>,
> "Paul Broeken" <pbroeken_at_casema.net> wrote:
>> How can I make it posssible ordering by ename without losing the
>hieracical
>> structure (with a ''connect by prior'').
>>
>> It's like
>>
>> select level,
>> empno,
>> ename
>> from emp
>> start with mgr is null
>> connect by prior empno=mgr
>>
>> If I use ''order by'' I lose the construction, and I cannot use a
>"view'' or
>> ''subquery'' cause of the ''connect by''.
>> Does anyone have an idea to solve this problem?
>> (it's the query I need for building a hierachical tree in Forms 6)
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Tue Jan 25 2000 - 15:01:46 CET