[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

distinct in oracle xmlagg

Posted on 2011-02-24
3
Medium Priority
?
5,061 Views
Last Modified: 2012-05-11
Experts.. I have a query which is below

 select rtrim(EXTRACT(XMLAGG (XMLELEMENT("s",a.src_id|| ',' )), '/s/text()').getstringval())
        into v_src_id_list
        from cfs a, cdr b
        where b.emp_oid = 235
        and b.enty_id= a.enty_id;

This is getting the list but some of the elements in the list are duplicates. for ex: when I print out the v_src_id_list I have 1,2,3,3,4,4,5 in it.

Is there any way to eliminate the duplicates in this list?
0
Comment
Question by:chickanna
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 34973670
Check out:
http://www.experts-exchange.com/Database/Oracle/Q_26845549.html


Try this:
select rtrim(EXTRACT(XMLAGG (XMLELEMENT("s",src_id|| ',' )), '/s/text()').getstringval())
        into v_src_id_list
        from ( select distinct a.src_id from cfs a, cdr b
        where b.emp_oid = 235
        and b.enty_id= a.enty_id);

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 34973673
query distinct values first then aggregate


 select rtrim(EXTRACT(XMLAGG (XMLELEMENT("s",src_id|| ',' )), '/s/text()').getstringval())
        into v_src_id_list
from
(select distinct a.src_id
        from cfs a, cdr b
        where b.emp_oid = 235
        and b.enty_id= a.enty_id)

0
 

Author Closing Comment

by:chickanna
ID: 34973785
thank you experts.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
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 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…
Suggested Courses

590 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