connect by prior child ,parent Object

select obj_name,obj_child,obj_parent,
      sys_connect_by_path(obj_name,'/')path,
      connect_by_root obj_name
      from cal_obj
      start with obj_parent is null
      connect by prior obj_child = obj_parent;

OBJ_NAME	OBJ_CHILD	OBJ_PARENT	PATH	      CONNECT_BY_ROOTOBJ_NAME
MainObject			  	          /MainObject    MainObject



all object in table
select * from cal_obj;

OBJ_CHILD	OBJ_NAME	OBJ_TYPE	OBJ_TITLE	DESCRIPTION	OBJ_PARENT
1193	 	SecondObject	Form						1192
1194		ThirdObject	Form						1193
		MainObject	Form			


i what a query to return 


OBJ_CHILD	OBJ_NAME	OBJ_TYPE	OBJ_TITLE	DESCRIPTION	OBJ_PARENT
SecondObject	MainObject	Form						
ThirdObject	SecondObject	Form					MainObject
		ThirdObject	Form			                                SecondObject


create table CAL_OBJ
(
  obj_child   NUMBER(4),
  obj_name    VARCHAR2(100),
  obj_type    VARCHAR2(50),
  obj_title   VARCHAR2(50),
  description VARCHAR2(50),
  obj_parent  NUMBER(5)
);

 insert into cal_obj(obj_parent,                       
                        obj_name,
                        obj_type)
                    values(1192,                          
                           'MainObject',
                           'Form');
                           
                           
                           
                           insert into cal_obj(obj_parent,
                                               obj_child,                       
                        obj_name,
                        obj_type)
                    values(1192, 
                            1193,                         
                           'SecondObject',
                           'Form');
                           
                               insert into cal_obj(obj_parent,
                                               obj_child,                       
                        obj_name,
                        obj_type)
                    values(1193, 
                            1194,                         
                           'ThirdObject',
                           'Form');

Open in new window

am in oracle database 11gR2
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
is there a question ?
0
chalie001Author Commented:
how can i display value like this from above table value
create table CAL_OBJ
(
  obj_child   NUMBER(4),
  obj_name    VARCHAR2(100),
  obj_type    VARCHAR2(50),
  obj_title   VARCHAR2(50),
  description VARCHAR2(50),
  obj_parent  NUMBER(5)
);


 insert into cal_obj(obj_child,                      
                        obj_name,
                        obj_type)
                    values(1192,                          
                           'MainObject',
                           'Form');
                           
                           
                           
                           insert into cal_obj(obj_parent,
                                               obj_child,                      
                        obj_name,
                        obj_type)
                    values(1192,
                            1193,                        
                           'SecondObject',
                           'Form');
                           
                               insert into cal_obj(obj_parent,
                                               obj_child,                      
                        obj_name,
                        obj_type)
                    values(1193,
                            1194,                        
                           'ThirdObject',
                           'Form');

check the attchament
LUT-PAYMENTMETHODS.xls
0
flow01Commented:
select
(select min(obj_name) from cal_obj c where c.obj_parent = o.obj_child) child_name  -- there can be more children : what do you want?
,o.obj_name   child_name
,o.obj_type
,o.obj_title
,o.description
, (select obj_name from cal_obj p where p.obj_child = o.obj_parent) parent_name
from cal_obj o
/
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chalie001Author Commented:
something like this but not hard coded
with

cal_obj as

(select 1193 OBJ_CHILD,'SecondObject' OBJ_NAME,'Form' OBJ_TYPE,null OBJ_TITLE,null DESCRIPTION,1192 OBJ_PARENT from dual union all

select 1194,'ThirdObject','Form',null,null,1193 from dual union all

select 1192,'MainObject','Form',null,null,null from dual

)

select (select obj_name from cal_obj where obj_parent = c.obj_child) obj_child,

       obj_name,obj_type,obj_title,description,

       (select obj_name from cal_obj where obj_child = c.obj_parent) obj_parent

  from cal_obj c

start with obj_parent is null

connect by prior obj_child = obj_parent

Open in new window

0
chalie001Author Commented:
i what connect prior between two table

create table OBJECT_LIST
(
  cal_objid   NUMBER(4) not null,
  obj_name    VARCHAR2(100),
  obj_type    VARCHAR2(20),
  obj_title   VARCHAR2(20),
  description VARCHAR2(50)
);

alter table OBJECT_LIST
  add constraint OBJECT_LIST_PK primary key (CAL_OBJID);
  
  
  create table CAL_ERD
(
  obj_child    NUMBER(5) not null,
  obj_parent   NUMBER(5) not null,
  obj_rel_type VARCHAR2(50)
);

alter table CAL_ERD
  add constraint CAL_ERD_PK primary key (OBJ_CHILD, OBJ_PARENT);
    
  alter table CAL_erd
  add constraint cal_parent_FK foreign key (obj_parent)
  references object_list (CAL_objid);
  
  
   alter table CAL_erd
  add constraint cal_child_FK foreign key (obj_child)
  references object_list (cal_objid);



insert into object_list (cal_objid,
                             obj_name,
                             obj_type)
                          values(1192,
                                'MainObject',
                                'Form');
                                
                                
             insert into object_list (cal_objid,
                             obj_name,
                             obj_type)
                          values(1193,
                                'SecondObject',
                                'Form');  
                                
                                
             insert into object_list (cal_objid,
                             obj_name,
                             obj_type)
                          values(1194,
                                'ThirdObject',
                                'Form');    
                                
                                
              insert into object_list (cal_objid,
                             obj_name,
                             obj_type)
                          values(1195,
                                'FourthObject',
                                'Form');  
                                
                                
    insert into object_list (cal_objid,
                             obj_name,
                             obj_type)
                      values(1196,
                             'FirthObject',
                             'Form');
                             
                         
                             
                             
                              insert into cal_erd values(1193,1192,null);
                              insert into cal_erd values(1194,1193,null);
                              insert into cal_erd values(1195,1194,null);

Open in new window


CAL_OBJID      OBJ_NAME              OBJ_TYPE      OBJ_TITLE      DESCRIPTION
1192              MainObject              Form            
1193              SecondObject              Form            
1194              ThirdObject              Form            
1195              FourthObject              Form            
1196              FirthObject              Form            



second table value
OBJ_CHILD      OBJ_PARENT      OBJ_REL_TYPE
1193              1192      
1194              1193      
1195              1194      



sql script i attempt
WITH t AS 
    (SELECT li.obj_name,
            li.obj_type,
            li.obj_title,
            li.DESCRIPTION, 
            LEVEL AS lvl,
            SYS_CONNECT_BY_PATH(li.obj_name, '/')paths 
    FROM object_list li,cal_erd er
     where li.cal_objid = er.obj_parent(+)
         START WITH obj_parent IS NULL
         CONNECT BY
                  NOCYCLE PRIOR obj_child = obj_parent)
  SELECT obj_name,
         LEAD(obj_name) OVER(ORDER BY paths) AS child_name,
         obj_type ,
         obj_title ,
         DESCRIPTION ,
         substr(paths,
                       INSTR(paths, '/', 1, CASE WHEN lvl > 1 THEN lvl-1 END) + 1 , 
                       INSTR(paths, '/', 1, CASE WHEN lvl > 1 THEN lvl END)                    
                      - INSTR(paths, '/', 1, CASE WHEN lvl > 1 THEN lvl-1 END) - 1 ) AS parent_name
    FROM t;

Open in new window


value am geting
OBJ_NAME      CHILD_NAME      OBJ_TYPE      OBJ_TITLE      DESCRIPTION      PARENT_NAME
FirthObject      FourthObject      Form                  
FourthObject                                          Form                  



i what value to be like this
OBJ_CHILD      OBJ_NAME       OBJ_TYPE    OBJ_TITLE    DESCRIPTION    OBJ_PARENT

SecondObject   MainObject     Form          

ThirdObject    SecondObject   Form                                                               MainObject

fourthObject   ThirdObject    Form                                                                  SecondObject

if possible like to display ojb_name id
0
flow01Commented:
you are changing the question !! There are other tables now !!

select obj_child, child_id, obj_id, obj_name, obj_parent, parent_id
from
(
select oc.obj_name obj_child,
       ec.obj_child child_id,
       c.cal_objid obj_id, c.obj_name, c.obj_type, c.obj_title, c.description,
       op.obj_name obj_parent,
       ep.obj_parent parent_id
  from object_list c,  
  cal_erd ec,
  object_list oc,
  cal_erd ep ,
  object_list op
  where ec.obj_parent(+) = c.cal_objid  -- if object is no parent still get an empty row
  and   ec.obj_child  = oc.cal_objid(+) -- if no child relation found stil get an empty row to get empty child-data
  and   ep.obj_child(+)  = c.cal_objid  -- if object is not a child still get an empty row
  and   ep.obj_parent = op.cal_objid(+) -- if no parent relation found stil get an empty row to get empty parent-data
)
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chalie001Author Commented:
it was correct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.