developers

    Forum Stats

  • 3,873,861 Users
  • 2,266,623 Discussions
  • 7,911,645 Comments

Discussions

Xquery with Union

TrojanSpirit
TrojanSpirit Member Posts: 27
edited Nov 7, 2012 6:22PM in XQuery
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?

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Nov 7, 2012 5:25PM Answer ✓
    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

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Nov 7, 2012 5:25PM Answer ✓
    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
    TrojanSpirit Member Posts: 27
    edited Nov 7, 2012 6:22PM
    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
This discussion has been closed.
developers