OraFAQ Forum: SQL & PL/SQL » Lookup Parent Values to Generate a String

Home » SQL & PL/SQL » SQL & PL/SQL » Lookup Parent Values to Generate a String

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lookup Parent Values to Generate a String [message #1509]

Tue, 07 May 2002 09:06

Rob
Messages: 70
Registered: January 2000

Member

I have a table where data is organized into multiple levels. I have the need to pass into a procedure the lowest level and perform a lookup up each level to the top of the hierarchy. The data looks like this...

PK Level Value ParentValue
101 1 USA
102 2 WA 101
103 3 Seattle 102

I need to be able to output something like this:
Seattle/WA/USA

I am familar with cursors to generate a running total, is there a way to do this with cursors? I appologize if this is a simple question, but I am very much a newbie.

Any help is greatly appreciated...

Report message to a moderator

Re: Lookup Parent Values to Generate a String [message #1510 is a reply to message #1509]

Tue, 07 May 2002 10:11

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

hi!.
are u lookin into something like this? if not, let me know.
i have use the standard oracle demo table emp here.
empno,ename,mgr(mgr=manager id, where again it is an employeed id of the manager).
*********************************************
SQL> get f1
1 create or replace procedure p1 as
2 retval varchar2(2000);
3 cursor c1 is select ename from emp
4 connect by prior empno=mgr
5 start with mgr is null;
6 begin
7 for crec in c1 loop
8 if c1%rowcount=1 then
9 retval:= crec.ename;
10 else
11 retval:=crec.ename||'/'||retval;
12 end if;
13 end loop;
14 dbms_output.put_line(retval);
15* end;
SQL> /

Procedure created.

SQL> exec p1
MILLER/CLARK/JAMES/TURNER/MARTIN/WARD/ALLEN/BLAKE/SMITH/FORD/ADAMS/SCOTT/JONES/KING

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Lookup Parent Values to Generate a String [message #1536 is a reply to message #1509]

Thu, 09 May 2002 15:26

Rob
Messages: 70
Registered: January 2000

Member

Thank You! This works great!

Report message to a moderator

Previous Topic:	recusion & curor
Next Topic:	Personal Oracle Installation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 19:05:56 CDT 2024