Solved

connect by prior child ,parent Object

Posted on 2014-11-29
7
165 Views
Last Modified: 2014-12-13
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
0
Comment
Question by:chalie001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40473890
is there a question ?
0
 

Author Comment

by:chalie001
ID: 40473981
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
 
LVL 20

Expert Comment

by:flow01
ID: 40474736
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chalie001
ID: 40475230
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
 

Author Comment

by:chalie001
ID: 40475381
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
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 40476477
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
 

Author Closing Comment

by:chalie001
ID: 40497673
it was correct
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question