Extra SQL join added in a HQL left outer join causes problem

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I have an HQL query:

Code:

    "SELECT DISTINCT ir.id, ir.weeksToPromoteStart, ir.weeksToPromoteStart, ir.auditInfo,"
      + " ir.risk, ir.item.itemUPC, ir.item.itemName, ir.item.top20, ir.item.itemSize, ir.item.itemPack, ir.costs.trend,"
      + " ir.costs.currentCostRange.lowCost, ir.costs.currentCostRange.highCost,"
      + " ir.costs.projectedCostRange.lowCost, ir.costs.projectedCostRange.highCost, ir.costs.currentCostsLastUpdated,"
      + " ir.item.subCommodity.comp_id.commodity.description, ir.item.subCommodity.description, ir.fieldOffice.name, ir.comments.comments, "
      + " ir.growingRegions.elements, ir.available "
      + " FROM ItemReport as ir left join ir.growingRegions.elements as gr where 1 = 1"
+
 " ORDER BY ir.item.subCommodity.description, ir.item.subCommodity.comp_id.commodity.description, ir.item.itemUPC, ir.id ";

There are multiple other criteria added to the where clause (hence the 1=1).

ItemReport has a M:1 with Item, a M:1 with Field Office, 1:1 with Costs, 1:1 with Comments, 1:M with Photos and 1:M with Growing Regions.

When I run my code, the SQL generated yields:

Code:

select distinct itemrepo0_.itm_rpt_id as x0_0_, itemrepo0_.wek_too_pro_stt_dt as x1_0_, itemrepo0_.wek_too_pro_stt_dt as x2_0_, itemrepo0_.lst_upd_ts as x3_0_, itemrepo0_.crt_ts as x3_1_, itemrepo0_.upd_usr_id as x3_2_, itemrepo0_.crt_usr_id as x3_3_, itemrepo0_.ris_cd as x4_0_, item3_.itm_upc_cd as x5_0_, item3_.itm_nam_tx as x6_0_, item3_.top_020_cd as x7_0_, item3_.itm_siz_tx as x8_0_, item3_.itm_pak_qy as x9_0_, costs6_.ted_cd as x10_0_, costs6_.cur_low_cst_am as x11_0_, costs6_.cur_hgh_cst_am as x12_0_, costs6_.pjt_low_cst_am as x13_0_, costs6_.pjt_hgh_cst_am as x14_0_, costs6_.lst_upd_ts as x15_0_, commodit5_.com_dsc_tx as x16_0_, subcommo4_.scm_dsc_tx as x17_0_, fieldoff7_.fld_ofe_nam_tx as x18_0_, comments8_.cmt_tx as x19_0_, growingr9_.grw_rgn_id as x20_0_, itemrepo0_.avl_cd as x21_0_ from item_report itemrepo0_ left outer join report_region_link growingr1_ on itemrepo0_.itm_rpt_id=growingr1_.itm_rpt_id left outer join growing_region growingr2_ on growingr1_.grw_rgn_id=growingr2_.grw_rgn_id, item item3_, sub_commodity subcommo4_, commodity commodit5_, costs costs6_, field_office fieldoff7_, comments comments8_, report_region_link growingr9_ where itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd and itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd and subcommo4_.com_cd=commodit5_.com_cd and subcommo4_.dpt_cd=commodit5_.dpt_cd and itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and itemrepo0_.itm_rpt_id=costs6_.cst_id and itemrepo0_.fld_ofe_id=fieldoff7_.fld_ofe_id and itemrepo0_.itm_rpt_id=comments8_.cmt_id and itemrepo0_.itm_rpt_id=growingr9_.itm_rpt_id and ((1=1 )) order by  subcommo4_.scm_dsc_tx , commodit5_.com_dsc_tx , item3_.itm_upc_cd , itemrepo0_.itm_rpt_id

Notice the ',report_region_link growingr9_' that is added right before the where clause. This ends up not displaying ItemReports that don't have growing_regions associated with them. Is this necessary? Any thoughts on why this is being added back in?

The SQL would normally be: item_report left outer join report_region_link left outer join growing_region, item, sub_commodity, commodity, costs, field_office, comments

The same query (sans the ',report_region_link growingr9_') works:

select distinct itemrepo0_.itm_rpt_id as x0_0_, itemrepo0_.wek_too_pro_stt_dt as x1_0_, itemrepo0_.wek_too_pro_stt_dt as x2_0_, itemrepo0_.lst_upd_ts as x3_0_, itemrepo0_.crt_ts as x3_1_, itemrepo0_.upd_usr_id as x3_2_, itemrepo0_.crt_usr_id as x3_3_, itemrepo0_.ris_cd as x4_0_, item3_.itm_upc_cd as x5_0_, item3_.itm_nam_tx as x6_0_, item3_.top_020_cd as x7_0_, item3_.itm_siz_tx as x8_0_, item3_.itm_pak_qy as x9_0_, costs6_.ted_cd as x10_0_, costs6_.cur_low_cst_am as x11_0_, costs6_.cur_hgh_cst_am as x12_0_, costs6_.pjt_low_cst_am as x13_0_, costs6_.pjt_hgh_cst_am as x14_0_, costs6_.lst_upd_ts as x15_0_, commodit5_.com_dsc_tx as x16_0_, subcommo4_.scm_dsc_tx as x17_0_, fieldoff7_.fld_ofe_nam_tx as x18_0_, comments8_.cmt_tx as x19_0_, growingr1_.grw_rgn_id as x20_0_, itemrepo0_.avl_cd as x21_0_ from item_report itemrepo0_ left outer join report_region_link growingr1_ on itemrepo0_.itm_rpt_id=growingr1_.itm_rpt_id left outer join growing_region growingr2_ on growingr1_.grw_rgn_id=growingr2_.grw_rgn_id, item item3_, sub_commodity subcommo4_, commodity commodit5_, costs costs6_, field_office fieldoff7_, comments comments8_ where itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd and itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd and subcommo4_.com_cd=commodit5_.com_cd and subcommo4_.dpt_cd=commodit5_.dpt_cd and itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and itemrepo0_.itm_rpt_id=costs6_.cst_id and itemrepo0_.fld_ofe_id=fieldoff7_.fld_ofe_id and itemrepo0_.itm_rpt_id=comments8_.cmt_id and ((1=1 )) order by subcommo4_.scm_dsc_tx , commodit5_.com_dsc_tx , item3_.itm_upc_cd , itemrepo0_.itm_rpt_id

Any thoughts on this? I'll look into createSqlQuery, but I'm puzzled why it's adding the GrowingRegion back on the end.

I can provide the config files if necessary.

emmanuel · **Posted:** Tue Nov 25, 2003 6:58 am

What if you try

Code:

FROM ItemReport as ir left join ir.growingRegions tmp left join tmp.elements gr

Not sure of the second left, might be an inner

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I'll give it a go and let you know the results. Thanks for the tip.

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

Rather:

Code:

net.sf.hibernate.QueryException: could not resolve property: elements of: com.paraware.GrowingRegion [SELECT DISTINCT ir.id, ir.weeksToPromoteStart, ir.weeksToPromoteStart, ir.auditInfo, ir.risk, ir.item.itemUPC, ir.item.itemName, ir.item.top20, ir.item.itemSize, ir.item.itemPack, ir.costs.trend, ir.costs.currentCostRange.lowCost, ir.costs.currentCostRange.highCost, ir.costs.projectedCostRange.lowCost, ir.costs.projectedCostRange.highCost, ir.costs.currentCostsLastUpdated, ir.item.subCommodity.comp_id.commodity.description, ir.item.subCommodity.description, ir.fieldOffice.name, ir.comments.comments,  gr, ir.available  FROM com.paraware.ItemReport as ir left join ir.growingRegions tmp left join tmp.elements gr  where 1 = 1 ORDER BY ir.item.subCommodity.description, ir.item.subCommodity.comp_id.commodity.description, ir.item.itemUPC, ir.id ]
   at net.sf.hibernate.persister.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:35)
   at net.sf.hibernate.hql.PathExpressionParser.getPropertyType(PathExpressionParser.java:242)
   at net.sf.hibernate.hql.FromPathExpressionParser.end(FromPathExpressionParser.java:12)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
   at net.sf.hibernate.hql.FromParser.token(FromParser.java:193)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:151)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:140)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:291)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1501)
   at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1531)
   at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:33)

emmanuel · **Posted:** Tue Nov 25, 2003 9:57 am

Hum, I don't know why ir.growingRegions.elements works then.

Can you post your mapping ?

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

Here are the mappings:

ItemReport

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

    <class name="com.paraware.ItemReport" table="item_report">

        <id name="id" type="string" unsaved-value="0" >
            <column name="itm_rpt_id" sql-type="char(32)" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>
        <property name="available" column="avl_cd" type="boolean" />
        <property name="peakCrop" column="pek_dt" type="date" />
        <property name="weeksToPromoteStart" column="wek_too_pro_stt_dt" type="date" />
        <property name="weeksToPromoteEnd" column="wek_too_pro_end_dt" type="date" />
        <property name="risk" column="ris_cd"  type="com.paraware.Risk" />
        <property name="urgent" column="urg_cd" type="boolean" />
            
      <many-to-one name="item" class="com.paraware.Item" not-null="true">
         <column name="itm_upc_cd" sql-type="char(13)"/>
      </many-to-one>
      
      <many-to-one name="fieldOffice" class="com.paraware.FieldOffice" >
         <column name="fld_ofe_id" sql-type="char(32)" not-null="true"/>
      </many-to-one>
      
      <one-to-one name="costs" class="com.paraware.Costs" cascade="all" />
      
      <one-to-one name="comments" class="com.paraware.Comments" cascade="all" />
      
      <set name="photos" table="photo"
         cascade="all" inverse="true" lazy="true">
         <key >
            <column name="itm_rpt_id" sql-type="char(32)"/>
         </key>
         <one-to-many class="com.paraware.Photo"/>
      </set>
      
      <set name="growingRegions" table="report_region_link"
         cascade="save-update" lazy="true" >
         <key >
            <column name="itm_rpt_id" sql-type="char(32)"/>
         </key>
         <many-to-many class="com.paraware.GrowingRegion" >
            <column name="grw_rgn_id" sql-type="char(32) "/>
         </many-to-many>
      </set>
      
      <set name="selectedAttributeValues" table="selected_attribute_value"
         cascade="all" inverse="true" lazy="true">
         <key >
            <column name="itm_rpt_id" sql-type="char(32)"/>
         </key>
         <one-to-many class="com.paraware.SelectedAttributeValue"/>
      </set>
      
      <set name="users" table="favorite_item_reports" 
         cascade="save-update" lazy="true">
         <key >
            <column name="itm_rpt_id" sql-type="char(32)"/>
         </key>
         <many-to-many class="com.paraware.User" >
            <column name="usr_id" sql-type="char(32)"/>
         </many-to-many>
      </set>
      
      <property name="auditInfo" type="com.paraware.AuditInfoType">
           <column name="lst_upd_ts"/>
           <column name="crt_ts"/>
           <column name="upd_usr_id" sql-type="char(8)" length="8"/>
           <column name="crt_usr_id" sql-type="char(8)" length="8"/>
       </property>
       
    </class>

</hibernate-mapping>

Item

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class name="com.paraware.Item"
      table="item">
      <id name="itemUPC" type="java.lang.String" >
         <column name="itm_upc_cd" sql-type="char(13)"/>
         <generator class="assigned" />
      </id>
      <property name="itemName" column="itm_nam_tx" type="string" length="50"
         />
      <property name="itemSize" type="string" >
         <column name="itm_siz_tx" sql-type="char(10)" />
      </property>
      <property name="itemPack" column="itm_pak_qy" type="int" />
      <property name="top20" column="top_020_cd" type="boolean" />
      <property name="palletTi" column="plt_lyr_qy" type="int" />
      <property name="palletHi" column="plt_ter_qy" type="int" />
      <property name="casesPerPallet" column="plt_cas_qy" type="int" />
      <property name="clusterType" column="cue_typ_cd"
         type="com.paraware.ClusterType"
         />
      <!-- associations -->
      <!-- bi-directional many-to-one association to SubCommodity -->
      <many-to-one name="subCommodity"
         class="com.paraware.SubCommodity"
         not-null="true" >
         <column name="scm_cd" sql-type="char(5)"/>
         <column name="com_cd" sql-type="char(3)"/>
         <column name="dpt_cd" sql-type="char(2)"/>
      </many-to-one>
      <set name="itemReports" table="item_report" cascade="all" inverse="true"
         lazy="true">
         <key>
            <column name="itm_upc_cd" sql-type="char(13)"/>
         </key>
         <one-to-many
            class="com.paraware.ItemReport"
            />
      </set>
      <property name="lastUpdated" type="java.sql.Timestamp"
         column="lst_upd_ts" not-null="true" length="26" />
   </class>
</hibernate-mapping>

GrowingRegion

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

    <class name="com.paraware.GrowingRegion" table="growing_region">

        <id name="id" type="string" unsaved-value="0">
            <column name="grw_rgn_id" sql-type="char(32)" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>

        <property name="name" type="string">
         <column name="grw_rgn_nam_tx" length="40" not-null="true"/>
      </property>
        <property name="code" type="string">
           <column name="grw_rgn_cd" sql-type="char(3)"  length="3" unique="true" not-null="true"/>   
        </property>

        <set name="itemReports" table="report_region_link"
         cascade="save-update" lazy="true">
         <key>
            <column name="grw_rgn_id" sql-type="char(32)"/>
         </key>
         <many-to-many class="com.paraware.ItemReport" >
            <column name="itm_rpt_id" sql-type="char(32)"/>
         </many-to-many>
      </set>

      <property name="auditInfo" type="com.paraware.AuditInfoType">
           <column name="lst_upd_ts"/>
           <column name="crt_ts"/>
           <column name="upd_usr_id" sql-type="char(8)" length="8"/>
           <column name="crt_usr_id" sql-type="char(8)" length="8"/>
       </property>
      
    </class>

</hibernate-mapping>

FieldOffice

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

    <class name="com.paraware.FieldOffice" table="field_office">

        <id name="id" type="string" unsaved-value="0">
            <column name="fld_ofe_id" sql-type="char(32)" length="32" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>

        <property name="name" type="string">
           <column name="fld_ofe_nam_tx" length="40" not-null="true"/>
          </property>
           
        <property name="code" type="string">
           <column name="fld_ofe_cd" sql-type="char(3)"  length="3" unique="true" not-null="true"/>
        </property>
        
      <set name="itemReports" table="item_report"
         cascade="all" inverse="true" lazy="true">
         <key column="fld_ofe_id"/>
         <one-to-many class="com.paraware.ItemReport"/>
      </set>
      
      <set name="users" table="user_field_office_link"
         cascade="none" lazy="true" >
         <key>
            <column name="fld_ofe_id" sql-type="char(32)"/>
         </key>
         <many-to-many class="com.paraware.User" >
            <column name="usr_id" sql-type="char(32)"/>
         </many-to-many>
      </set>

      <property name="auditInfo" type="com.paraware.AuditInfoType">
           <column name="lst_upd_ts"/>
           <column name="crt_ts"/>
           <column name="upd_usr_id" sql-type="char(8)" length="8"/>
           <column name="crt_usr_id" sql-type="char(8)" length="8"/>
       </property>

    </class>

</hibernate-mapping>

Department

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

<class 
    name="com.paraware.Department" 
    table="department"
>

    <id
        name="departmentCode"
        type="java.lang.String" length="2" >
      <column name="dpt_cd" sql-type="char(2)" not-null="true" />        
        <generator class="assigned" />
    </id>

    <property
        name="description"
        type="java.lang.String" length="30">
        <column name="dpt_dsc_tx" sql-type="varchar(30)"
        not-null="true"/>
        
        </property>
    <property
        name="lastUpdated"
        type="java.sql.Timestamp"
        column="lst_upd_ts"
        not-null="true"
        length="26"
    />

    <!-- associations -->
    <!-- bi-directional one-to-many association to Commodity -->
    <set
        name="commodities"
        lazy="true"
        inverse="true"
    >
        <key>
            <column name="dpt_cd" />
        </key>
        <one-to-many 
            class="com.paraware.Commodity"
        />
    </set>

</class>
</hibernate-mapping>

Commodity

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class name="com.paraware.Commodity"
      table="commodity" >
      <composite-id name="comp_id"
         class="com.paraware.CommodityPK"
         >
         <key-property name="commodityCode" type="java.lang.String"
            length="3">
            <column name="com_cd" sql-type="char(3)"/>
         </key-property>
         <!-- bi-directional many-to-one association to Department -->
         <key-many-to-one name="department"
            class="com.paraware.Department"
            >
            <column name="dpt_cd" sql-type="char(2)"/>
         </key-many-to-one>
      </composite-id>
      <property name="description" type="java.lang.String" column="com_dsc_tx"
         not-null="true" length="30" />
      <property name="lastUpdated" type="java.sql.Timestamp"
         column="lst_upd_ts" not-null="true" length="26" />
      <!-- associations -->
      <!-- bi-directional one-to-many association to Subcommodity -->
      <set name="subCommodities" lazy="true" cascade="all" inverse="true" >
         <key>
            <column name="com_cd" />
            <column name="dpt_cd" />
         </key>
         <one-to-many
            class="com.paraware.SubCommodity"
            />
      </set>
      <set name="attributes" table="attribute" cascade="all" inverse="true"
         lazy="true">
         <key>
            <column name="com_cd"/>
            <column name="dpt_cd"/>
         </key>
         <one-to-many
            class="com.paraware.Attribute"
            />
      </set>
   </class>
</hibernate-mapping>

SubCommodity

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

<class 
    name="com.paraware.SubCommodity" 
    table="sub_commodity"
>

    <composite-id name="comp_id" class="com.paraware.SubCommodityPK">
        <key-property 
            name="subCommodityCode" 
            type="java.lang.String"
            length="5"
        >
        <column name="scm_cd" sql-type="char(5)"/>
        </key-property>
        <!-- bi-directional many-to-one association to Commodity -->
        <key-many-to-one
           name="commodity"
           class="com.paraware.Commodity"
       >
           <column name="com_cd" sql-type="char(3)"/>
           <column name="dpt_cd" sql-type="char(2)"/>
       </key-many-to-one>
    </composite-id>    

        <property name="description" type="java.lang.String">
            <column name="scm_dsc_tx" sql-type="varchar(55)" not-null="true" length="55"/>
        </property>

    <property
        name="lastUpdated"
        type="java.sql.Timestamp"
        column="lst_upd_ts"
        not-null="true"
        length="26"
    />

    <!-- associations -->
    <!-- bi-directional one-to-many association to Item -->
    <set
        name="items"
        lazy="true"
        inverse="true"
        cascade="all"
    >
    <!-- order of these columns is CRITICAL that it match the order in Items, because query is crafted based on this -->
   <!-- if the order is not correct, you may receive the following error on DB2 (if so, crank up the logging -->
   <!-- SQL0302N   The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. -->
        <key>
            <column name="scm_cd" sql-type="char(5)"/>
            <column name="com_cd" sql-type="char(3)"/>
            <column name="dpt_cd" sql-type="char(2)"/>
        </key>
        <one-to-many 
            class="com.paraware.Item"
        />
    </set>

</class>
</hibernate-mapping>

Comments

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

    <class name="com.paraware.Comments" table="comments">

        <id name="id" type="string" unsaved-value="0">
            <column name="cmt_id" length="32" sql-type="char(32)" not-null="true"/>
            <generator class="uuid.hex"/>
        </id>

        <property name="comments" column="cmt_tx" length="255"/>
      <one-to-one name="itemReport" class="com.paraware.ItemReport" constrained="true" />
      
    </class>

</hibernate-mapping>

Costs

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
   <class name="com.paraware.Costs"
      table="costs">
      <id name="id" type="string" unsaved-value="0">
         <column name="cst_id" sql-type="char(32)" length="32" not-null="true"/>
         <generator class="uuid.hex"/>
      </id>
      <component name="currentCostRange"
         class="com.paraware.CostRange">
         <property name="lowCost" type="big_decimal">
            <column name="cur_low_cst_am" sql-type="DECIMAL(15, 2)"/>
         </property>
         <property name="highCost" type="big_decimal">
            <column name="cur_hgh_cst_am" sql-type="DECIMAL(15, 2)"/>
         </property>
         <property name="averageCost" type="big_decimal">
            <column name="cur_avg_cst_am" sql-type="DECIMAL(15, 2)"/>
         </property>
      </component>
      <component name="projectedCostRange"
         class="com.paraware.CostRange">
         <property name="lowCost" type="big_decimal">
            <column name="pjt_low_cst_am" sql-type="DECIMAL(15, 2)"/>
         </property>
         <property name="highCost" type="big_decimal">
            <column name="pjt_hgh_cst_am" sql-type="DECIMAL(15, 2)"/>
         </property>
         <property name="averageCost" type="big_decimal">
            <column name="pjt_avg_cst_am" sql-type="DECIMAL(15, 2)"/>
         </property>
      </component>
      <property name="trend" column="ted_cd" 
         type="com.paraware.Trend"/>
      <property name="currentCostsLastUpdated" column="lst_upd_ts"
         type="timestamp"/>
      <one-to-one name="itemReport"
         class="com.paraware.ItemReport"
         constrained="true"
         />
   </class>
</hibernate-mapping>

emmanuel · **Posted:** Tue Nov 25, 2003 10:22 am

We will do agile help ;-)

Why did you use .elements ?

Does this work

Code:

FROM ItemReport as ir left join ir.growingRegions gr

?

Where did you find the ir.growingRegions.elements way to do it ?
Shouldn't it be elements(ir.growingRegions) ?

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

Last post:
http://forum.hibernate.org/viewtopic.php?t=924713

Yes, post above indicates that it's deprecated in the FROM clause, so your suggestion may do the trick. I'll try it now.

Brian

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I do appreciate the agile help. Thanks for responding.

I changed it to:

Code:

FROM ItemReport as ir left join ir.growingRegions as gr where 1 = 1

and the query executed as (I left it out of code so I could show you join that it's inserting that is giving me the fits--if this wasn't there, then the query would be correct.)

select distinct itemrepo0_.itm_rpt_id as x0_0_, itemrepo0_.wek_too_pro_stt_dt as x1_0_, itemrepo0_.wek_too_pro_stt_dt as x2_0_, itemrepo0_.lst_upd_ts as x3_0_, itemrepo0_.crt_ts as x3_1_, itemrepo0_.upd_usr_id as x3_2_, itemrepo0_.crt_usr_id as x3_3_, itemrepo0_.ris_cd as x4_0_, item3_.itm_upc_cd as x5_0_, item3_.itm_nam_tx as x6_0_, item3_.top_020_cd as x7_0_, item3_.itm_siz_tx as x8_0_, item3_.itm_pak_qy as x9_0_, costs6_.ted_cd as x10_0_, costs6_.cur_low_cst_am as x11_0_, costs6_.cur_hgh_cst_am as x12_0_, costs6_.pjt_low_cst_am as x13_0_, costs6_.pjt_hgh_cst_am as x14_0_, costs6_.lst_upd_ts as x15_0_, commodit5_.com_dsc_tx as x16_0_, subcommo4_.scm_dsc_tx as x17_0_, fieldoff7_.fld_ofe_nam_tx as x18_0_, comments8_.cmt_tx as x19_0_, growingr9_.grw_rgn_id as x20_0_, itemrepo0_.avl_cd as x21_0_ from item_report itemrepo0_ left outer join report_region_link growingr1_ on itemrepo0_.itm_rpt_id=growingr1_.itm_rpt_id left outer join growing_region growingr2_ on growingr1_.grw_rgn_id=growingr2_.grw_rgn_id, item item3_, sub_commodity subcommo4_, commodity commodit5_, costs costs6_, field_office fieldoff7_, comments comments8_, report_region_link growingr9_ where itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd and itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd and subcommo4_.com_cd=commodit5_.com_cd and subcommo4_.dpt_cd=commodit5_.dpt_cd and itemrepo0_.itm_upc_cd=item3_.itm_upc_cd and itemrepo0_.itm_rpt_id=costs6_.cst_id and itemrepo0_.fld_ofe_id=fieldoff7_.fld_ofe_id and itemrepo0_.itm_rpt_id=comments8_.cmt_id and itemrepo0_.itm_rpt_id=growingr9_.itm_rpt_id and ((1=1 )) order by subcommo4_.scm_dsc_tx , commodit5_.com_dsc_tx , item3_.itm_upc_cd , itemrepo0_.itm_rpt_id

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I also thought moving the other "inner joins" to the left of the "left join" to see if that would help. Your thoughts?

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I also tried:

Code:

FROM ItemReport as ir, ir.costs, ir.comments, ir.item, ir.item.subCommodity, ir.item.subCommodity.comp_id.commodity, ir.fieldOffice left join ir.growingRegions as gr

This resulted in all of the inner joins before the left outer joins, except for the bolded problem area shown below (Drat):

select distinct itemrepo0_.itm_rpt_id as x0_0_, itemrepo0_.wek_too_pro_stt_dt as x1_0_, itemrepo0_.wek_too_pro_stt_dt as x2_0_, itemrepo0_.lst_upd_ts as x3_0_, itemrepo0_.crt_ts as x3_1_, itemrepo0_.upd_usr_id as x3_2_, itemrepo0_.crt_usr_id as x3_3_, itemrepo0_.ris_cd as x4_0_, item3_.itm_upc_cd as x5_0_, item3_.itm_nam_tx as x6_0_, item3_.top_020_cd as x7_0_, item3_.itm_siz_tx as x8_0_, item3_.itm_pak_qy as x9_0_, costs1_.ted_cd as x10_0_, costs1_.cur_low_cst_am as x11_0_, costs1_.cur_hgh_cst_am as x12_0_, costs1_.pjt_low_cst_am as x13_0_, costs1_.pjt_hgh_cst_am as x14_0_, costs1_.lst_upd_ts as x15_0_, commodit5_.com_dsc_tx as x16_0_, subcommo4_.scm_dsc_tx as x17_0_, fieldoff6_.fld_ofe_nam_tx as x18_0_, comments2_.cmt_tx as x19_0_, growingr9_.grw_rgn_id as x20_0_, itemrepo0_.avl_cd as x21_0_ from item_report itemrepo0_ inner join costs costs1_ on itemrepo0_.itm_rpt_id=costs1_.cst_id inner join comments comments2_ on itemrepo0_.itm_rpt_id=comments2_.cmt_id inner join item item3_ on itemrepo0_.itm_upc_cd=item3_.itm_upc_cd inner join sub_commodity subcommo4_ on item3_.scm_cd=subcommo4_.scm_cd and item3_.com_cd=subcommo4_.com_cd and item3_.dpt_cd=subcommo4_.dpt_cd inner join commodity commodit5_ on subcommo4_.com_cd=commodit5_.com_cd and subcommo4_.dpt_cd=commodit5_.dpt_cd inner join field_office fieldoff6_ on itemrepo0_.fld_ofe_id=fieldoff6_.fld_ofe_id left outer join report_region_link growingr7_ on itemrepo0_.itm_rpt_id=growingr7_.itm_rpt_id left outer join growing_region growingr8_ on growingr7_.grw_rgn_id=growingr8_.grw_rgn_id, report_region_link growingr9_ where itemrepo0_.itm_rpt_id=growingr9_.itm_rpt_id and ((1=1 )) order by subcommo4_.scm_dsc_tx , commodit5_.com_dsc_tx , item3_.itm_upc_cd , itemrepo0_.itm_rpt_id

emmanuel · **Posted:** Tue Nov 25, 2003 10:46 am

Actually growingr9_ is needed by "ir.growingRegions.elements" or elements(ir.growingRegions)

Try elements(gr), instead of elements(ir.growingRegions)

gavin · **Posted:** Tue Nov 25, 2003 10:59 am

How about you simplify the query down massively and isolate the problem. This query is way to big to really debug and see what comes from where.

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I tried elements(gr) (and actually gr.elements - which should be the same). The result was:

could not resolve property: elements of: com.paraware.GrowingRegion [SELECT DISTINCT ir.id, ir.weeksToPromoteStart, ir.weeksToPromoteStart, ir.auditInfo, ir.risk, ir.item.itemUPC, ir.item.itemName, ir.item.top20, ir.item.itemSize, ir.item.itemPack, ir.costs.trend, ir.costs.currentCostRange.lowCost, ir.costs.currentCostRange.highCost, ir.costs.projectedCostRange.lowCost, ir.costs.projectedCostRange.highCost, ir.costs.currentCostsLastUpdated, ir.item.subCommodity.comp_id.commodity.description, ir.item.subCommodity.description, ir.fieldOffice.name, ir.comments.comments, gr.elements, ir.available FROM com.paraware.ItemReport as ir left join ir.growingRegions as gr where 1 = 1 ORDER BY ir.item.subCommodity.description, ir.item.subCommodity.comp_id.commodity.description, ir.item.itemUPC, ir.id ]

Any other ideas?

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

I'll include a smaller test case which has a 1:1 and a M:M from the primary element to a sibling and a child.

I'll be back shortly with it.