Solved

concaternate connect prior return values

Posted on 2014-12-22
9
114 Views
Last Modified: 2015-01-11
hi i like to concaternate my return value e.g if an object got more than one parent i what to return all parent for that object in one column,i what to avoid duplicate values
i what values to be like this
    Obj_Name       child_name      Parent_name

    MainObject     ThirdObject    (null)

    SecondObject   FourthObject    MainObject

    FourthObject   ThirdObject     SecondObject

    ThirdObject    ThirdObject     SecondObject,MainObject
my sql is
select obj_name, obj_child, obj_parent, child_id, obj_id,  parent_id
 from
 (
 select c.obj_name,
          oc.obj_name obj_child, 
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.cal_objid obj_id,
        c.obj_type,
        c.obj_title,
        c.description,
        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
   )

Open in new window

0
Comment
Question by:chalie001
  • 4
  • 4
9 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 40513726
try
select obj_name, obj_child, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents, child_id, obj_id --,  parent_id
 from
 (
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.cal_objid obj_id,
        c.obj_type,
        c.obj_title,
        c.description,
        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
   )
group by obj_name, obj_child,  child_id, obj_id
/
0
 
LVL 31

Expert Comment

by:awking00
ID: 40513733
Rather than showing your sql, can you post some sample relevant (not extraneous fields) data for the object_list and cal_erd tables and what you expect as results?
0
 

Author Comment

by:chalie001
ID: 40514475
hi this is my sample
create table CAL_OBJ(  obj_name    VARCHAR2(100) not null,  obj_type    VARCHAR2(50),  obj_title   VARCHAR2(50),  description VARCHAR2(500),  sub_system  VARCHAR2(100),  status      VARCHAR2(20));alter table CAL_OBJ  add constraint CAL_OBJ_PK primary key (OBJ_NAME)create table CAL_ERD_LINK(  obj_child    VARCHAR2(100) not null,  obj_parent   VARCHAR2(100) not null,  obj_rel_type VARCHAR2(50))alter table CAL_ERD_LINK  add constraint CAL_ERD_LINK primary key (OBJ_CHILD, OBJ_PARENT)    alter table CAL_ERD_LINK  add constraint CAL_CHILD_LINKFK foreign key (OBJ_CHILD)  references CAL_OBJ (OBJ_NAME);alter table CAL_ERD_LINK  add constraint CAL_PARENT_LINK_FK foreign key (OBJ_PARENT)  references CAL_OBJ (OBJ_NAME);


insert into cal_obj (obj_name,
                             obj_type)
                          values('MainObject',
                                'Form');
                                
                                
             insert into cal_obj (obj_name,
                             obj_type)
                          values('SecondObject',
                                'Form');  
                                
                                
             insert into cal_obj (obj_name,
                             obj_type)
                          values('ThirdObject',
                                'Form');
                                
                                
             insert into cal_obj (obj_name,
                             obj_type)
                          values('FourthObject',
                                'Form');                      
                                
                                select * from cal_erd_link
                                
                                select * from cal_obj
                                where obj_name in ('MainObject','SecondObject','ThirdObject')
          
          insert into cal_erd_link values('SecondObject','MainObject',null);
          
           insert into cal_erd_link values('ThirdObject','MainObject',null);
           
            insert into cal_erd_link values('ThirdObject','SecondObject',null);
            
            insert into cal_erd_link values('FourthObject','SecondObject',null);



the query returning value like

OBJ_NAME	OBJ_CHILD	OBJ_PARENT
MainObject	ThirdObject	
MainObject	SecondObject	
ThirdObject	MainObject
SecondObject	ThirdObject	MainObject
SecondObject	FourthObject	MainObject
ThirdObject		        SecondObject
FourthObject		        SecondObject





i what value to be like this


OBJ_NAME	OBJ_CHILD	          OBJ_PARENTS
MainObject	ThirdObject,SecondObject 		
ThirdObject	MainObject,SecondObject	
SecondObject	ThirdObject,FourthObject  MainObject
ThirdObject     			  SecondObject
FourthObject		                  SecondObject
  
my sql is
select obj_name, obj_child, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc,
   cal_erd_link ep ,
   cal_obj op
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject'

Open in new window

)
0
 
LVL 20

Expert Comment

by:flow01
ID: 40514986
select obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc,
   cal_erd_link ep ,
   cal_obj op
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
)
group by obj_name
/
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: 40516115
am geting this values,why am i geting 2 same values as parent
OBJ_NAME      OBJ_CHILDS                  OBJ_PARENTS
FourthObject      SecondObject
MainObject      SecondObject,ThirdObject      
SecondObject      FourthObject,ThirdObject                      MainObject,MainObject
ThirdObject                                    MainObject,SecondObject



i must have this value
OBJ_NAME      OBJ_CHILDS                  OBJ_PARENTS
MainObject      SecondObject                        
SecondObject      FourthObject                                      MainObject
ThirdObject      ThirdObject                                      SecondObject,MainObject
0
 
LVL 20

Expert Comment

by:flow01
ID: 40516304
Because there in the original query there are 2 rows for second object with different childs and the same parent

I don't understand your must have values :  you don't need thirdobject as child of main and second object anymore ?  And what is the thirdobject doing as child of thirdobject ?

Ignoring this I focus on eliminating the double values.
Be aware that if in het long run there will be objects with many childs or many parents you will hit limitatations!

2 ways:
1 based on the original query (this will be probably not the best for performance
2 a new query that first gathers the  childs , then the parents and combines them afterwards
with qry1
as
(
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc,
   cal_erd_link ep ,
   cal_obj op
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
)
select
--c.obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
c.obj_name, obj_childs, obj_parents
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct obj_name, obj_child
 from qry1
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct obj_name, obj_parent
 from qry1
 )
 group by obj_name
 ) p
where c.obj_name = p.obj_name
--group by c.obj_name


select
c.obj_name, obj_childs, obj_parents
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) p
where c.obj_name = p.obj_name
0
 

Author Comment

by:chalie001
ID: 40520421
i what to return column Obj_type,description from cal_obj table how will i do that
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 40520976
one way would be

select
c.obj_name, d.Obj_type, d.description , obj_childs, obj_parents
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name
0
 

Author Closing Comment

by:chalie001
ID: 40542950
thanks its 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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

746 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

10 Experts available now in Live!

Get 1:1 Help Now