SQL & PL/SQL

Hi,

I am using (xmlagg(xmlelement) function to concatenate multiple row data on a specific column. My query and output is shown below:


select distinct ap.invoice_id,
ap.dist_code_combination_id,
rtrim(xmlagg(xmlelement(e,ph.segment1,',').extract('//text()')),',') segment1,
pf2.first_name
||' '
||pf2.last_name po_requester
From Apps.Ap_Invoice_Distributions_All Ap,
apps.ap_invoices_all i,
Apps.Po_Distributions_All Pda,
Apps.Po_Headers_All Ph,
Apps.Po_Lines_All Pl,
Apps.Po_Req_Distributions_All Prh,
apps.PO_REQUISITION_LINES_ALL prl,
apps.po_requisition_headers_all pha,
Apps.Fnd_User U,
Apps.Per_People_F Pf,
apps.per_people_f pf2
where ap.invoice_id(+) = i.invoice_id
AND ap.po_distribution_id = pda.po_distribution_id(+)
AND pda.po_header_id = ph.po_header_id(+)
AND pl.po_line_id(+) = pda.po_line_id
AND pda.req_distribution_id = prh.distribution_id(+)
AND prh.requisition_line_id = prl.requisition_line_id(+)
AND pha.requisition_header_id(+) = prl.requisition_header_id
AND pha.preparer_id = u.employee_id(+)
AND pha.preparer_id = pf.person_id(+)
and prl.to_person_id = pf2.person_id (+)
and ap.line_type_lookup_code = 'ITEM'
and ap.dist_code_combination_id = 5012
and ap.invoice_id = 37196
group by ap.invoice_id, ap.dist_code_combination_id,
pf2.first_name
||' '
||pf2.last_name


Output:

"INVOICE_ID""DIST_CODE_COMBINATION_ID" "SEGMENT1" "PO_REQUESTER"
37196 5012 "100124,100124,100124,100124,101527,101546,100124,100124" "Anna Golda"

The segment1 column contains duplicate segment numbers, how can the duplicates be removed in the function? Please note I am using Oracle Financials database version 10 and unable to use WM_CONCAT(WMSYS schema does not exist) and LISTAGG functions.

Thanks

Edited by: sliderrules on 18-Jun-2010 05:26

Edited by: sliderrules on 18-Jun-2010 05:27