| recursive sql? Im stuck [message #373079] |
Mon, 26 March 2001 10:42  |
JOHN
Messages: 182
Registered: April 1998
|
Senior Member |
|
|
I have a DB table (category) which contains three columns (name, id, and parentId). If I had the following three records.
bedroom,1,null
bed,2,1
sheet,3,2
I would like to write some sql that could print out the following.
bedroom
bedroom>bed
bedroom>bed>sheet
I have no idea how to do this... could someone lend a helping hand?
thanks... if this isnt the right forum could you point me in the right direction.
john
|
|
|
|
| Re: recursive sql? Im stuck [message #373111 is a reply to message #373079] |
Wed, 28 March 2001 01:11  |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Hi John,
I couldn't find a pure SQL solution and I really doubt there is one. I got I working with a packaged
funtion and a recursive START WITH ... CONNECT BY statement. The trick is to use the LEVEL info from this statement to pad the string parts
passed to the function which stores the stuff in a pl/sql table in the package body. Here we go ...
create table t (name varchar2(40), id integer, parent_id integer);
insert into t values ('bedroom',1,null );
insert into t values ('bed',2,1);
insert into t values ('sheet',3,2);
insert into t values ('table',4,1);
insert into t values ('cup',5,4);
insert into t values ('pillow',6,2);
create or replace package strpkg as
function str (strlvl in integer, strname in varchar2) return varchar2;
PRAGMA RESTRICT_REFERENCES(str, WNDS);
end;
/
show errors
create or replace package body strpkg as
TYPE strtab_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
strtab strtab_type;
function str (strlvl in integer, strname in varchar2) return varchar2 is
begin
if strlvl = 1 then
strtab(strlvl) := strname;
return strtab(strlvl);
elsif strlvl > 1 then
strtab(strlvl) := strtab(strlvl-1)||'>'||strname;
return strtab(strlvl);
else
return ('Invalid level');
end if;
end;
end;
/
show errors
SQL> select strpkg.str(level,name)
2 from t
3 start with id=1
4 connect by prior id=parent_id;
STRPKG.STR(LEVEL,NAME)
---------------------------------------
bedroom
bedroom>bed
bedroom>bed>sheet
bedroom>bed>pillow
bedroom>bed>anotherpillow
bedroom>table
bedroom>table>cup
7 rows selected.
Cheers, Joachim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
