Xquery with Union

TrojanSpirit

As I am not able to use IF-ELSE in Oracle for select statements, I am using UNION to discard one select query result set and to get resultset of other query depending upon the Parameter value.

Example of Normal Query:

Select FNAME , LNAME from EMP where Param.1 in ( 'NAME' , 'SURNAME' )

UNION

SELECT CITY , ADDRESS from EMP where Param.1 in ( 'LOCATION' , 'ADDRESS')

so when I give Param.1 as "NAME", it gives me the result set of first query while it discards resultset of other query as it will be null coz Param.1 i snot in 'Location' or 'ADDRESS".

Same wau if I give Param.1 as "ADDRESS", it gives me the resultset of 2nd query and discards 1st query resultset as it is null.

But same approach doesnot work for Oracle query with Xquery for following:

Select rtrim(XMLELEMENT( "Rowset" , XMLAGG ( RW.column_value) ) , ',' ) AS Orders
from EMPTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT) AS RW

WHERE

[Param.1] IN ( 'NAME' , 'SURNAME' )

UNION

Select rtrim(XMLELEMENT( "Rowset" , XMLAGG ( RW.column_value) ) , ',' ) AS Orders
from ADDRESSTABLE UL, XMLTABLE('Rowsets/Rowset/Row' PASSING UL.TEXT) AS RW

WHERE

[Param.1] IN ( 'LOCATION' , 'ADDRESS' )


Here, if the Param.1 is "NAME" then the outcomes as <Rowset></Rowset> + Output of other query (which only thing I want)

How can i remove this <Rowset></Rowset> from the output?

odie_63

As I am not able to use IF-ELSE in Oracle for select statements

How about a CASE statement?
If each query is always meant to return a single aggregated column (and of course only one row), then you can use this :

SELECT CASE WHEN param_1 IN ('VAL1', 'VAL2')
             THEN (
               SELECT ...
               FROM emptable, XMLTable( ... )
             )
            WHEN param_1 IN ('VAL3', 'VAL4')
             THEN (
               SELECT ...
               FROM addresstable, XMLTable( ... )
             )
        END AS Orders
FROM dual;

If you prefer the UNION approach, then :

Here, if the Param.1 is "NAME" then the outcomes as <Rowset></Rowset> + Output of other query (which only thing I want)

That's because of the XMLAgg aggregate function.
An aggregate function without GROUP BY always returns one row, even though there's no row in the original result set.

SQL> SELECT XMLElement("Rowset", XMLAgg(rw.column_value))
  2  FROM emptable ul
  3     , XMLTable('/Rowsets/Rowset/Row' passing ul.text) as rw
  4  WHERE 1 = 0
  5  ;
 
XMLELEMENT("ROWSET",XMLAGG(RW.
--------------------------------------------------------------------------------
<Rowset></Rowset>
 

You can group by an empty list of column to force the required behaviour :

SQL> SELECT XMLElement("Rowset", XMLAgg(rw.column_value))
  2  FROM emptable ul
  3     , XMLTable('/Rowsets/Rowset/Row' passing ul.text) as rw
  4  WHERE 1 = 0
  5  GROUP BY ()
  6  ;
 
XMLELEMENT("ROWSET",XMLAGG(RW.
--------------------------------------------------------------------------------
 

You should also use UNION ALL instead of UNION so that Oracle doesn't bother performing any unneeded sort operation.

What is the purpose of RTRIM in your examples? I don't see any reason to call it over an XMLType instance, furthermore it performs an implicit datatype conversion to VARCHAR2 which can potentially cause errors.
If you need to serialize the resulting XMLType column then use the appropriate methods getStringVal(), getClobVal() or XMLSerialize function (11g).

Edited by: odie_63 on 7 nov. 2012 23:24

odie_63

As I am not able to use IF-ELSE in Oracle for select statements

How about a CASE statement?
If each query is always meant to return a single aggregated column (and of course only one row), then you can use this :

SELECT CASE WHEN param_1 IN ('VAL1', 'VAL2')
             THEN (
               SELECT ...
               FROM emptable, XMLTable( ... )
             )
            WHEN param_1 IN ('VAL3', 'VAL4')
             THEN (
               SELECT ...
               FROM addresstable, XMLTable( ... )
             )
        END AS Orders
FROM dual;

If you prefer the UNION approach, then :

Here, if the Param.1 is "NAME" then the outcomes as <Rowset></Rowset> + Output of other query (which only thing I want)

That's because of the XMLAgg aggregate function.
An aggregate function without GROUP BY always returns one row, even though there's no row in the original result set.

SQL> SELECT XMLElement("Rowset", XMLAgg(rw.column_value))
  2  FROM emptable ul
  3     , XMLTable('/Rowsets/Rowset/Row' passing ul.text) as rw
  4  WHERE 1 = 0
  5  ;
 
XMLELEMENT("ROWSET",XMLAGG(RW.
--------------------------------------------------------------------------------
<Rowset></Rowset>
 

You can group by an empty list of column to force the required behaviour :

SQL> SELECT XMLElement("Rowset", XMLAgg(rw.column_value))
  2  FROM emptable ul
  3     , XMLTable('/Rowsets/Rowset/Row' passing ul.text) as rw
  4  WHERE 1 = 0
  5  GROUP BY ()
  6  ;
 
XMLELEMENT("ROWSET",XMLAGG(RW.
--------------------------------------------------------------------------------
 

You should also use UNION ALL instead of UNION so that Oracle doesn't bother performing any unneeded sort operation.

What is the purpose of RTRIM in your examples? I don't see any reason to call it over an XMLType instance, furthermore it performs an implicit datatype conversion to VARCHAR2 which can potentially cause errors.
If you need to serialize the resulting XMLType column then use the appropriate methods getStringVal(), getClobVal() or XMLSerialize function (11g).

Edited by: odie_63 on 7 nov. 2012 23:24

TrojanSpirit

Hi Odie,

As Always you are a savior !

I completely forgot that my query returns only one Row so I can very well use Case -When -End.

I followed your advice and it suffices my requirement. Thanks a ton for the help

Edited by: 967327 on Nov 7, 2012 3:22 PM