Home » SQL & PL/SQL » SQL & PL/SQL » Can't write my SQL recursive request (Oracle 10)
icon9.gif  Can't write my SQL recursive request (Oracle 10) [message #208943] Tue, 12 December 2006 12:11 Go to next message
Gaelle
Messages: 2
Registered: December 2006
Location: France
Junior Member
Hi !

I have a SQL request problem : I don't manage to write what I want...
I have two tables (in Oracle 10) :
MODEL
_____
MDL_CODE
MDL_LABEL

and

SUB_MODEL
_________
MDL_CODE_SON
MDL_CODE_FATHER

It is recursive on table SUB_MODEL, where MDL_CODE_SON can be a MDL_CODE_FATHER and son on. I would like to be able, for a given model m in table MODEL, to get all the path
MODEL1/MODEL2/.../MODELN/m
I'm trying to use keywords like sys_connect_by_path(MDL_LABEL, '/'), but I never manage to get the full path.
For example, the very first model of the path misses, since it has no father model.

Can someone help me ?

Thanks so much if so !

GaŽlle.
Re: Can't write my SQL recursive request (Oracle 10) [message #209048 is a reply to message #208943] Wed, 13 December 2006 01:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I suppose we *could* help, but then we would need sample data. This means: (simplified) CREATE TABLE scripts, INSERT statements for sample data and your desired output based on that sample data.

MHE
Re: Can't write my SQL recursive request (Oracle 10) [message #209060 is a reply to message #209048] Wed, 13 December 2006 02:33 Go to previous messageGo to next message
Gaelle
Messages: 2
Registered: December 2006
Location: France
Junior Member
OK ! Thanks so much for try and help me, here is some more information:

create table MODEL (
MDL_CODE VARCHAR2(30) not null,
MDL_LABEL VARCHAR2(80) not null,
constraint PK_MODEL primary key (MDL_CODE)
);
create table SUB_MODEL (
MDL_CODE_SON VARCHAR2(30) not null,
MDL_CODE_FATHER VARCHAR2(30) not null,
constraint PK_MODEL primary key (MDL_CODE_SON, MDL_CODE_FATHER)
);


and insert statements:
insert into MODEL values ('model1', 'model 1');
insert into MODEL values ('model2', 'model 2');
insert into MODEL values ('model3', 'my model');

insert into SUB_MODEL values ('model3', 'model2');
insert into SUB_MODEL values ('model2', 'model1');


...and what I'm trying to get is :
model 1/model 2/my model
It seems like I can't create a view on my database (thanks to my database admin), so I'm now investigating on stored procedures, but still haven't found... Sad

Re: Can't write my SQL recursive request (Oracle 10) [message #209140 is a reply to message #208943] Wed, 13 December 2006 08:43 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
There might be neater ways of doing this (hierarchical queries have never been my strongest area), but you could do:

SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(mdl_label_son, '/') "Path"
   FROM
   (
    select mdl_label as mdl_label_son,
           null as mdl_label_father,
           mdl_code as mdl_code_son,
		   null as mdl_code_father
      from model m
	  where mdl_code = 'model1'
   union all
   select m1.mdl_label as mdl_label_son,
           m2.mdl_label as mdl_label_father,
           s.mdl_code_son,
		   s.mdl_code_father
      from model m1, model m2, sub_model s
	  where s.mdl_code_son = m1.mdl_code
	  and   s.mdl_code_father = m2.mdl_code
	  )
   START WITH mdl_code_son = 'model1'
   CONNECT BY PRIOR mdl_code_son = mdl_code_father;


gives:

Path
 /model 1
   /model 1/model 2
     /model 1/model 2/my model
Previous Topic: order by
Next Topic: Oracle 9i client installation failed
Goto Forum:
  


Current Time: Sun Dec 04 04:55:16 CST 2016

Total time taken to generate the page: 0.08750 seconds