Following on from this answered thread:
1090215
Whereby the last poster recommended the use of
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (segment1|| ',' AS "Seg")
)
ORDER BY segment1 ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)
To get a distinct list of elements. Works great, but I actually need to use the concept of "distinct immediately preceding".
Sample data:
create table xml_test
(emp_number number, seq number)
insert into xml_test values ('12345',1);
insert into xml_test values ('23456',2);
insert into xml_test values ('44323',3);
insert into xml_test values ('12345',4);
insert into xml_test values ('12345',5);
using previous post suggestion:
select
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (emp_number|| ',' AS "emp")
)
ORDER BY seq ASC
).EXTRACT ('./E[not(@emp = preceding-sibling::E/@emp)]/@emp'),
','
) who
from xml_test
returns
WHO
--------------------------
12345,23456,44323
I have a unique requirement here, to return:
WHO
--------------------------
12345,23456,44323,12345
** note only 12345 once after 44323. In essence, I'm looking for a way to return the employee number when the employee number is not equal to the immediately preceding sibling. **
I've tried with the following (based on some readings of XPATH):
select
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (emp_number||',' AS "emp")
)
ORDER BY seq ASC
).EXTRACT ('./E[not(@emp = preceding-sibling::node()[position()-1])]/@emp'),
','
) who2
from xml_test
returns:
WHO
-------------------
12345,23456,44323,12345,12345
But it's not removing the last 12345. Anyone know where I'm going wrong?