I have one large table "transaction" and it is mapped to a BaseTransaction object. This base object has numerous subclasses that are mapped to the same transaction table. For some reason, query 1 (see running code section) below works (returns results) and query 2 does not (returns empty List). According to the log, the queries that Hibernate is running *both* produce records. I am including the mapping below as well. I would think that it has to do with the mapping but they look identical to me. There is no exception thrown...just different results. At the bottom, I am also posting the generated SQL from Hibernate. Both of these produce resulting records. One thing that I notice is that the "working" sql also contains the sql for the joining mapped classes where the "non-working" code does not. However, I don't think that I am controlling that. Any suggestions would be most appreciated.

Travis



Running code:
<pre>
String sql = null;
switch (flag) {
case 1: //results in records returned
sql = "from com.novainfo.emt.domain.VisaTransaction as tran " +
"where tran.typeCode.description = 'Visa Outgoing' " +
"and tran.status.description = 'New'";
break;
case 2: //returns empty result list
sql = "from com.novainfo.emt.domain.JcbTransaction as tran " +
"where tran.typeCode.description = 'Jcb Funded Outgoing' " +
"and tran.status.description = 'New'";
break;
default:
}
Database db = null;
try {
Class[] classes =
new Class[] {BaseTransaction.class,
MerchantNumberType.class,
TransactionType.class,
TranCode.class,
TransactionStatus.class};
db = DbFactory.getInstance().getDatabase(classes);
db.open();
java.util.List results = db.query(sql);
System.err.println(results);
} catch(Exception e) {
e.printStackTrace();
} finally {
if (db != null) {
try {
db.close();
} catch(Exception e) {}
}
}
</pre>


hbm.xml:
<pre>
<hibernate-mapping>
<class name="com.novainfo.emt.domain.BaseTransaction" table="transaction" discriminator-value="B">
<id name="id" type="long" unsaved-value="0" column="tran_id">
<generator class="sequence">
<param name="sequence">emt_id_sequence</param>
</generator>
</id>
<discriminator column="card_type" type="character"/>
<property name="merchName">
<column name="merch_name" sql-type="string" not-null="false"/>
</property>
<property name="merchNum">
<column name="merch_num" sql-type="string" not-null="false"/>
</property>
<property name="refNum">
<column name="ref_num" sql-type="string" not-null="false"/>
</property>
<property name="tranAmt">
<column name="tran_amt" sql-type="double" not-null="false"/>
</property>
<property name="reportDate">
<column name="report_date" sql-type="date" not-null="false"/>
</property>
<property name="cardHolderNum">
<column name="card_holder_num" sql-type="string" not-null="false"/>
</property>
<many-to-one name="merchNumType"
class="com.novainfo.emt.domain.MerchantNumberType"
column="merch_num_type_id"
cascade="none"/>
<many-to-one name="typeCode"
class="com.novainfo.emt.domain.TransactionType"
column="type_code_id"
cascade="none"/>
<many-to-one name="tranCode"
class="com.novainfo.emt.domain.TranCode"
column="tran_code_id"
cascade="none"/>
<many-to-one name="status"
class="com.novainfo.emt.domain.TransactionStatus"
column="tran_status_id"
cascade="none"/>
<subclass name="com.novainfo.emt.domain.VisaTransaction" discriminator-value="V">
<property name="clientGrp">
<column name="client_group" sql-type="string" not-null="false"/>
</property>
<property name="source">
<column name="source" sql-type="string" not-null="false"/>
</property>
<property name="paperItem">
<column name="paper_item_flag" sql-type="boolean" not-null="false"/>
</property>
<property name="reReject">
<column name="rereject_flag" sql-type="boolean" not-null="false"/>
</property>
<property name="rejectCode">
<column name="reject_code" sql-type="string" not-null="false"/>
</property>
<property name="acceptorId">
<column name="acceptor_id" sql-type="string" not-null="false"/>
</property>
<property name="represent">
<column name="represent_flag" sql-type="boolean" not-null="false"/>
</property>
</subclass>
<subclass name="com.novainfo.emt.domain.JcbTransaction" discriminator-value="J">
<property name="jcbMerchNum">
<column name="assn_merch_num" sql-type="string" not-null="false"/>
</property>
<property name="tranDate">
<column name="tran_date" sql-type="date" not-null="false"/>
</property>
<property name="funded">
<column name="funded_flag" sql-type="boolean" not-null="false"/>
</property>
<property name="interchanged">
<column name="interchanged_flag" sql-type="boolean" not-null="false"/>
</property>
<property name="reason">
<column name="reason" sql-type="string" not-null="false"/>
</property>
</subclass>
</class>
</hibernate-mapping>
</pre>


Generated SQL:
<pre>
--Working SQL for VisaTransaction:
select visatransa0_.tran_id as tran_id,
visatransa0_.client_group as client_13_,
visatransa0_.source as source,
visatransa0_.paper_item_flag as paper_i15_,
visatransa0_.rereject_flag as rerejec16_,
visatransa0_.reject_code as reject_17_,
visatransa0_.acceptor_id as accepto18_,
visatransa0_.represent_flag as represe19_,
visatransa0_.merch_name as merch_name,
visatransa0_.merch_num as merch_num,
visatransa0_.ref_num as ref_num,
visatransa0_.tran_amt as tran_amt,
visatransa0_.report_date as report_d7_,
visatransa0_.card_holder_num as card_hol8_,
visatransa0_.merch_num_type_id as merch_nu9_,
visatransa0_.type_code_id as type_co10_,
visatransa0_.tran_code_id as tran_co11_,
visatransa0_.tran_status_id as tran_st12_
from transaction visatransa0_,
transaction_type transactio1_,
transaction_status transactio2_
where visatransa0_.card_type='V'
and ((transactio1_.description='Visa Outgoing' and visatransa0_.type_code_id=transactio1_.transaction_type_id)
and(transactio2_.description='New' and visatransa0_.tran_status_id=transactio2_.tran_status_id))


select merchantnu0_.merchant_number_type_id as merchant1_0_,
merchantnu0_.description as descript2_0_
from merchant_number_type merchantnu0_
where merchantnu0_.merchant_number_type_id=?

select transactio0_.transaction_type_id as transact1_0_,
transactio0_.description as descript2_0_
from transaction_type transactio0_
where transactio0_.transaction_type_id=?

select trancode0_.tran_code_id as tran_cod1_0_,
trancode0_.tran_code as tran_code0_,
trancode0_.description as descript3_0_
from tran_code trancode0_
where trancode0_.tran_code_id=?

select transactio0_.tran_status_id as tran_sta1_0_,
transactio0_.description as descript2_0_
from transaction_status transactio0_
where transactio0_.tran_status_id=?


--Non Working version for JcbTransaction
select jcbtransac0_.tran_id as tran_id,
jcbtransac0_.assn_merch_num as assn_me20_,
jcbtransac0_.tran_date as tran_date,
jcbtransac0_.funded_flag as funded_22_,
jcbtransac0_.interchanged_flag as interch23_,
jcbtransac0_.reason as reason,
jcbtransac0_.merch_name as merch_name,
jcbtransac0_.merch_num as merch_num,
jcbtransac0_.ref_num as ref_num,
jcbtransac0_.tran_amt as tran_amt,
jcbtransac0_.report_date as report_d7_,
jcbtransac0_.card_holder_num as card_hol8_,
jcbtransac0_.merch_num_type_id as merch_nu9_,
jcbtransac0_.type_code_id as type_co10_,
jcbtransac0_.tran_code_id as tran_co11_,
jcbtransac0_.tran_status_id as tran_st12_
from transaction jcbtransac0_,
transaction_type transactio1_,
transaction_status transactio2_
where jcbtransac0_.card_type='J'
and ((transactio1_.description='Jcb Funded Outgoing' and jcbtransac0_.type_code_id=transactio1_.transaction_type_id)
and
(transactio2_.description='New' and jcbtransac0_.tran_status_id=transactio2_.tran_status_id))
</pre>