2 Replies Latest reply: Nov 7, 2012 5:22 PM by TrojanSpirit RSS

    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?
        • 1. Re: Xquery with Union
          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
          • 2. Re: Xquery with Union
            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