Re: How to tweak hierarchial SQL queries?

In article <NEWTNews.817690699.11000.bruce_at_ga.bst.bls.com> Bruce Douglas <bruce.douglas_at_bridge.bst.bls.com> writes:
>From: Bruce Douglas <bruce.douglas_at_bridge.bst.bls.com>
>Subject: How to tweak hierarchial SQL queries?
>Date: Wed, 29 Nov 95 16:05:30 PDT

>As an Oracle newbie, I've recently discovered the START WITH... CONNECT BY
>clauses in SQL. It is really neat, but.....
 

>1. Is there any way to affect the order in which "siblings" are returned? For
>example, we have a parent-child table containing locations. I'd like to see
>this result from a query:
 

>Arizona
> Phoenix
> Tempe
>Arkansas
> Fayetteville
> Little Rock
> Texarkana
> ...
 

>I can't get the "states" and "cities" to come back in alphabetical order.

And I don't think you'll ever find a way to do that--connectby/startwith actually defines the order--so trying to order by something else doesn't make a lot of sense. Of course you *may* order by the pseudocols (like level) and that can often be very useful.

>2. Is there a way to sneak past the prohibition against joins with CONNECT BY?

The only way that I know of (and this only works if you've got PL/SQL 2.1 or above) is to create a package function the accepts your foreign key as a parameter and returns the value that you want. Remember to include a restrict_references pragma for the function in the package spec.

Then you could do something like this:

rpt_ln                            rpt_ln_hier
------                            -----------
rpt_ln_id       number            rpt_ln_id      number
rpt_ln_id_desc  varchar2(30)      par_rpt_ln_id  number

select
       rpt_ln_id
      ,pkg.function(rpt_ln_id)  rpt_ln_id_desc
from
       rpt_ln_hier
connect by prior
       rpt_ln_id = par_rpt_ln_id
start with
       par_rpt_ln_id is null

Hope that helps...

Mike

>Thanks in advance for your help
 

>--------------------------------------------------------
>Bruce Douglas -- BellSouth Telecommunications
><Bruce.Douglas_at_bridge.bst.bls.com>
>--------------------------------------------------------
Received on Thu Nov 30 1995 - 00:00:00 CET