Solved

connect by prior child ,parent Object

Posted on 2014-11-29
7
154 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
  • 4
  • 2
7 Comments
 
LVL 36

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now