Solved

concaternate connect prior return values

Posted on 2014-12-22
9
119 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 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
Technology Partners: 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!

 
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
 

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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 69
Distinct values from all columns in a table?? PL SQL 4 58
Can't Access My Database 57 84
oracle query 3 26
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

735 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