1 Reply Latest reply on Jul 4, 2005 12:00 PM by kasper moller

    FYI:  Declared-sql using SQL UNION

    Stephen Budewig Newbie

      Howdy all,

      Wanted to share my experience with the JBoss community just in case it might be helpful to someone else.

      The client had a functional SQL statement that contained JOINs and a UNION. The solution I used was to use an ejbSelect statement in the jbosscmp-jdbc.xml which looks like below:

       <entity>
       <ejb-name>T1</ejb-name>
       <table-name>T1</table-name>
       <cmp-field>
       <field-name>KF1</field-name>
       <column-name>KF1</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>KF2</field-name>
       <column-name>KF2</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>KF3</field-name>
       <column-name>KF3</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>F4</field-name>
       <column-name>F4</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>F5</field-name>
       <column-name>F5</column-name>
       </cmp-field>
       <query>
       <query-method>
       <method-name>ejbSelectAllByT1T2Union</method-name>
       <method-params>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       <method-param>java.lang.String</method-param>
       </method-params>
       </query-method>
       <declared-sql>
       <select>
       <distinct/>
       <ejb-name>T1</ejb-name>
       <alias>h1</alias>
       <!-- spb - must include the non-key fields in "additional-columns" -->
       <additional-columns>, F4, F5</additional-columns>
       </select>
       <from><![CDATA[
       INNER JOIN (
       select distinct KF1 from T1 HIST1
       where HIST1.KF3 = 'SOME_TYPE'
       and ((HIST1.KF2 <= {2}
       and (HIST1.F5 >= {1} or HIST1.F5 is null))
       and HIST1.F4 = {0})
       ) histJoin ON histJoin.KF1 = h1.KF1
       UNION
       SELECT h2.* FROM T1 h2
       INNER JOIN (
       select distinct tc.ID_EM from T2 tc
       where tc.ID_STR_RT = {0} and tc.WEEK_ENDING = {2}
       ) tcJoin ON tcJoin.ID_EM = h2.KF1
       ]]></from>
      
       </declared-sql>
       </query>
       </entity>
      


      Note that fields T1.KF1, T1.KF2 and T1.KF3 are key fields of table T1. The trick was to include
      <additional-columns>, F4, F5</additional-columns>
      which will allow the second select [after the union] to match the number of columns.

      Hope this saves someone a little time. :)

      Good luck,

      Stephen

        • 1. Re: FYI:  Declared-sql using SQL UNION
          kasper moller Newbie

          Hi there

          I am having some trouble deploying a modification of your example. I think it is because i don't know how to declare the method correctly in ejb-jar.xml.

          So far I have this in my ejb-jar :

          <query>
           <query-method>
           <method-name>ejbSelectAvailableCars</method-name>
           <method-params>
           <method-param>java.lang.String</method-param>
           <method-param>java.util.Date</method-param>
           <method-param>java.util.Date</method-param>
           <method-param>java.lang.String</method-param>
           </method-params>
           </query-method>
           <ejb-ql/>
           </query>
           </entity>


          And my jbosscmp-jdbc.xml looks like yours. With the same method name and signature as above.

          My jboss version is 4.0.2

          BR
          Kasper