Re: concat data from several rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Jul 1998 01:05:17 GMT
Message-ID: <35998ae7.423539@192.86.155.100>

A copy of this was sent to ndreon_at_my-dejanews.com (if that email address didn't require changing) On Tue, 30 Jun 1998 23:21:23 GMT, you wrote:

>I have a data records which have a parent record in the same table. Each
>record has a parent_id column that points to its parent record or is NULL. I
>want to concatenate the name of a child record with the names of all of its
>ancestors, something like:
>
> ... || grand_parent_name || parent_name || name
>
>I can write a PL/SQL function that uses CONNECT BY to do this but I must do it
>with a single SQL query and the query can not call my function (we are using a
>very lame COTS product).
>
>Does anyone know a way?

Why not hide the function in a view and query off of the view? eg:

create or replace function mgmt_chain( p_empno in number ) return varchar2 as

    l_str varchar2(2000) default null; begin

    for x in ( select ename

                 from emp
                connect by prior mgr = empno
                  start with empno = p_empno )
    loop
        l_str := l_str || '|' || x.ename;

    end loop;

    return ltrim( l_str, '|' );
end;
/

create or replace view emp_view
as
select a.empno, a.ename, mgmt_chain(a.mgr) mgmt_chain from emp a
/

the query tool can't tell you are using a function this way...

>
>Thanks
>Nathan
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 30 1998 - 20:05:17 CDT