Re: Hierarchical Query

From: Stéphane Faroult <sfaroult_at_oriolecorp.com>
Date: Sat, 23 Jan 1999 14:25:00 -0800
Message-ID: <36AA4C3C.1952@oriolecorp.com>

Fei Sha wrote:
>
> Hi, everybody,
>
> I want to do something with hierarchical query, but I don't know
> how to. And I wonder whether it is possible after all.
>
> Here is a table:
>
> TABLE T
> --------------------------------
> parent_id name child_id
> --------------------------------
> 1 A 2
> 2 B 3
> 3 C 4
> 4 D 5
> ...
> --------------------------------
>
> I want a query to return the trace of the hierarchy.
>
> i.e. A.B.C.D

Why don't you try PL/SQL ?
Something like

set serveroutput on
declare
  result_string varchar2(200) := '';
  cursor C is SELECT name

              FROM t
              START WITH parent_id = 1
              CONNECT BY prior child_id = parent_id;

begin
  for rec in c
  loop
    if (length(result_string) > 0)
    then
      result_string := result_string || '.';     end if;
    result_string := result_string || rec.name;  end loop;
 dbms_output.put_line(result_string);
end;

You may have problems with dbms_output.put_line, the argument of which is limited in length to 250 characters or so. You could write this as a PL/SQL function as well.
--
Regards,

  Stéphane Faroult
  Oriole Corporation Received on Sat Jan 23 1999 - 16:25:00 CST