Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

concaternate connect prior return values

Posted on 2014-12-22
9
Medium Priority
?
124 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 21

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 32

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 21

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
 

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 21

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 21

Accepted Solution

by:
flow01 earned 2000 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

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!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

824 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