Hi,
I have a strange problem when trying to use XMLAGG with DISTINCT.
A simple example:
Setup:
CREATE TABLE DEPT( DEPTNO NUMBER );
CREATE TABLE EMP( EMPNO NUMBER, DEPTNO NUMBER );
INSERT INTO DEPT( DEPTNO )
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
INSERT INTO EMP( EMPNO, DEPTNO )
SELECT ROWNUM, DEPTNO
FROM DEPT, ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3 );
DEPT:
DEPTNO
1
2
3
EMP:
EMPNO DEPTNO
1 1
4 1
7 1
2 2
5 2
8 2
3 3
6 3
9 3
Statement:
SELECT XMLELEMENT( "DEPT", (
SELECT XMLAGG( XMLELEMENT( "EMP", empno ) )
FROM (
SELECT e.empno FROM emp e WHERE e.deptno = d.deptno
)
)
).extract( '/' ) as xml
FROM dept d
WHERE d.deptno = 2
Result:
<DEPT>
<EMP>2</EMP>
<EMP>5</EMP>
<EMP>8</EMP>
</DEPT>
Adding DISTINCT.
Statement:
SELECT XMLELEMENT( "DEPT", (
SELECT XMLAGG( XMLELEMENT( "EMP", empno ) )
FROM (
SELECT DISTINCT e.empno FROM emp e WHERE e.deptno = d.deptno
)
)
).extract( '/' ) as xml
FROM dept d
WHERE d.deptno = 2
Result:
<DEPT>
<EMP>1</EMP>
<EMP>2</EMP>
<EMP>3</EMP>
<EMP>4</EMP>
<EMP>5</EMP>
<EMP>6</EMP>
<EMP>7</EMP>
<EMP>8</EMP>
<EMP>9</EMP>
</DEPT>
This differs from what I would expect which is the same result as the first statement produces.
(I know that the distinct does not make any sense in this simple example, but I need it in my real application)
Version:
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
PRODUCT VERSION STATUS
---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
NLSRTL 10.2.0.1.0 ; Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 ; 64bi
PL/SQL 10.2.0.1.0 ; Production
TNS for 64-bit Windows: 10.2.0.1.0 ; Production
I have searched for a solution for quite a while but could not find anything. I'm thankfull for any help.
[EDIT]
I forgot to say that the following statement produces the wanted result but is very inconvenient for me.
SELECT XMLELEMENT( "DEPT", (
SELECT XMLAGG( XMLELEMENT( "EMP", empno ) )
FROM (
SELECT DISTINCT e.empno FROM emp e WHERE e.deptno = 2
)
)
).extract( '/' ) as xml
FROM dept d
WHERE d.deptno = 2
Result:
<DEPT>
<EMP>2</EMP>
<EMP>5</EMP>
<EMP>8</EMP>
</DEPT>
Kind regards
Andi
Edited by: 924216 on 29.03.2012 02:54