Re: XMLAGG with analytic functions

On Jun 21, 5:43 pm, Matt T <puskas.d..._at_gmail.com> wrote:
> Anybody else had issues with values from analytic functions
> disappearing when wrapped in an XMLAGG function?
>
> Apologies for the noddy example!
>
> SQL> select *
> 2 from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
> PL/SQL Release 9.2.0.6.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for Solaris: Version 9.2.0.6.0 - Production
> NLSRTL Version 9.2.0.6.0 - Production
>
> 1* select xmlelement("TOTAL_EMP", count(*) over() ) FROM emp
> SQL> /
>
> XMLELEMENT("TOTAL_EMP",COUNT(*)OVER())
> --------------------------------------------------------------------------------
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
> <TOTAL_EMP>10</TOTAL_EMP>
>
> 1* select xmlagg(xmlelement("TOTAL_EMP", count(*) over() ) ) FROM
> emp
> SQL> /
>
> XMLAGG(XMLELEMENT("TOTAL_EMP",COUNT(*)OVER()))
> --------------------------------------------------------------------------------
> <TOTAL_EMP></TOTAL_EMP><TOTAL_EMP></TOTAL_EMP><TOTAL_EMP></
> TOTAL_EMP><TOTAL_EMP>

Yes, seems that way.
Well, as a workaround, you could put the analytic function in an inline view first:

select xmlagg(xmlelement("TOTAL_EMP", cnt) ) from
(
select count(*) over () cnt
from emp
)

HTH. Received on Thu Jun 21 2007 - 04:59:05 CDT