Re: SQL Puzzle

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 9 Feb 2010 16:48:17 -0800
Message-ID: <bf46381002091648r7a2ad864v6a8a3df343088d66_at_mail.gmail.com>

On Mon, Feb 8, 2010 at 1:53 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> RHAAAA ...
>
>   Why do you want some PL/SQL? Stuff the connect by (I don't expect
> 11GR2 yet :-)) into a subquery, add sys_connect_by_path to the select
> list of this subquery as ordering key, join and order by the ordering key.
> Should work.
>
>

It's OK Stephane, I used SQL. :)

Thanks for the clue about sys_connect_by_path, I have never really used it, and should have taken another look at the docs.

The sys_connect_by_path function gives the data I need, though I do need to parse it out. What I did doesn't sound like what you suggested, but it does allow me to accomplish exactly what I want.

Here's the query :

with a1 as (
select

   id
, level depth
, last_name
, first_name
, sys_connect_by_path(id, ':') || ':::::::' id_chain
from authors a
start with id = 1
connect by prior a.id = a.reports_to_id )
, l4 as (
select

   id
, last_name
, first_name
, id_chain
, substr(id_chain,instr(id_chain, ':', 1,1)+1, instr(id_chain, ':', 1,2)
- (instr(id_chain, ':', 1,1)+1 )) l1_id

, substr(id_chain,instr(id_chain, ':', 1,2)+1, instr(id_chain, ':', 1,3)
- (instr(id_chain, ':', 1,2)+1 )) l2_id

, substr(id_chain,instr(id_chain, ':', 1,3)+1, instr(id_chain, ':', 1,4)
- (instr(id_chain, ':', 1,3)+1 )) l3_id from a1
)
select

   l4.id l4_id
, id_chain
, decode(l4.last_name,null,'',l4.last_name || ',' || l4.first_name) L4
, l3.id l3_id
, decode(l3.last_name,null,'',l3.last_name || ',' || l3.first_name) l3
, l2.id l2_id
, decode(l2.last_name,null,'',l2.last_name || ',' || l2.first_name) l2
, l1.id l1_id
, decode(l1.last_name,null,'',l1.last_name || ',' || l1.first_name) l1
from l4

left outer join authors l3 on l3.id = l3_id
left outer join authors l2 on l2.id = l2_id
left outer join authors l1 on l1.id = l1_id

/

  L4_ID L4                     L3_ID L3                     L2_ID
L2                     L1_ID L1
------- -------------------- ------- -------------------- -------
-------------------- ------- --------------------
      1

Clemens,Samuel
1 Clemens,Samuel

      2 Twain,Mark                                              2
Twain,Mark                 1 Clemens,Samuel
      3 Delderfield,R.F.           3 Delderfield,R.F.           2
Twain,Mark                 1 Clemens,Samuel
      4 Wodehouse,P.G.             3 Delderfield,R.F.           2
Twain,Mark                 1 Clemens,Samuel
     10 Doyle,Arthur              10 Doyle,Arthur               2
Twain,Mark                 1 Clemens,Samuel
     12 Bradbury,Ray              12 Bradbury,Ray               2
Twain,Mark                 1 Clemens,Samuel
     17 Pournelle,Jerry           17 Pournelle,Jerry            2
Twain,Mark                 1 Clemens,Samuel
     19 Barnes,Steve              17 Pournelle,Jerry            2
Twain,Mark                 1 Clemens,Samuel
     18 Niven,Larry               18 Niven,Larry                2
Twain,Mark                 1 Clemens,Samuel
     21 Tolkien,J.R.R.            21 Tolkien,J.R.R.             2
Twain,Mark                 1 Clemens,Samuel
      5 Christie,Agatha                                         5
Christie,Agatha            1 Clemens,Samuel
      6 Hemingway,Ernest                                        6
Hemingway,Ernest           1 Clemens,Samuel
      7 Chandler,Raymond           7 Chandler,Raymond           6
Hemingway,Ernest           1 Clemens,Samuel
      8 Hammett,Dashiell           7 Chandler,Raymond           6
Hemingway,Ernest           1 Clemens,Samuel
      9 Conrad,Joseph              9 Conrad,Joseph              6
Hemingway,Ernest           1 Clemens,Samuel
     11 Burroughs,Edgar            9 Conrad,Joseph              6
Hemingway,Ernest           1 Clemens,Samuel
     13 Lovecraft,H.P.                                         13
Lovecraft,H.P.             1 Clemens,Samuel
     14 Heinlien,Robert           14 Heinlien,Robert           13
Lovecraft,H.P.             1 Clemens,Samuel
     15 Sturgeon,Theodore         15 Sturgeon,Theodore         13
Lovecraft,H.P.             1 Clemens,Samuel
     16 Pohl,Frederik             15 Sturgeon,Theodore         13
Lovecraft,H.P.             1 Clemens,Samuel
     20 Moses,Sam                                              20
Moses,Sam                  1 Clemens,Samuel

21 rows selected.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 09 2010 - 18:48:17 CST