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