Hibernate Community • View topic - HQL: Join - Column ... is invalid in the select list...

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

HQL: Join - Column ... is invalid in the select list...

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

nfung

Post subject: HQL: Join - Column ... is invalid in the select list...

Posted: Mon Sep 12, 2005 9:05 am

Regular

Joined: Mon May 30, 2005 11:20 pm
Posts: 66

Hi, trying to do a join with HQL and ran into this error:

Quote:

"Column 'items1_.item_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. "

The HQL statement is:

Code:

SELECT COUNT(*) FROM warehouse.to.inv_swatches AS sw INNER JOIN FETCH sw.Items AS invitem 

Any idea? My mapping files are included for your reference... Thanks in advance!

1.inv_swatches (Parent)

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.inv_swatches, WarehouseLib" table="inv_swatches">
      <id name="SwatchID" column="swatch_id" type="Int32" unsaved-value="0" >
         <generator class="identity" /> 
      </id>
      
      <many-to-one name="ParentInventoryMaster" column="inv_id" class="warehouse.to.zInventory, WarehouseLib" not-null="true" />
      ... other stuff
      
      <set name="Items" inverse="true" cascade="all" table="inv_item" lazy="false">
         <key column="swatch_id"/>
         <one-to-many class="warehouse.to.inv_item, WarehouseLib"/>
      </set>
      
   </class>
</hibernate-mapping>

2. inv_item (Child)

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="warehouse.to.inv_item, WarehouseLib" table="inv_item">
      <id name="ItemID" column="item_id" type="Int32" unsaved-value="0" >
         <generator class="identity" /> 
      </id> 
      
      <many-to-one name="ParentSwatches" column="swatch_id" class="warehouse.to.inv_swatches, WarehouseLib" not-null="true" />
      ... other stuff...
   </class>
</hibernate-mapping>

Top

nfung

Post subject:

Posted: Mon Sep 12, 2005 10:24 pm

Regular

Joined: Mon May 30, 2005 11:20 pm
Posts: 66

I used SQL Profiler to capture the generated statement:

Code:

select 
   items1_.item_id as item_id, 
   items1_.swatch_id as swatch_id, 
   items1_.location as location, 
   items1_.open_qty as open_qty, 
   items1_.reserved_qty as reserved5_, 
   items1_.available_qty as availabl6_, 
   items1_.purpose as purpose, 
   items1_.expiry_date as expiry_d8_, 
   items1_.CreateBy as CreateBy, 
   items1_.CreateDate as CreateDate, 
   items1_.LastUpdateBy as LastUpd11_, 
   items1_.LastUpdateDate as LastUpd12_, 
   items1_.item_id as item_id__, 
   items1_.swatch_id as swatch_id__, 
   COUNT(inv_swat0_.swatch_id) as x0_0_ 
   
   from inv_swatches inv_swat0_ 
      inner join inv_item items1_ 
      on inv_swat0_.swatch_id=items1_.swatch_id

I dont understand, the SELECT clause in HQL statement I wrote is:

Code:

SELECT COUNT(sw.SwatchID) FROM warehouse.to.inv_swatches AS sw INNER JOIN FETCH sw.Items AS invitem

I also tried:

Code:

SELECT COUNT(sw) FROM warehouse.to.inv_swatches AS sw INNER JOIN FETCH sw.Items AS invitem

And...

Code:

SELECT COUNT(*) FROM warehouse.to.inv_swatches AS sw INNER JOIN FETCH sw.Items AS invitem

None of which does not include field/column "item_id"! Running this statement manually results in:

Quote:

Column 'items1_.item_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Thanks in advance!

Top

TheShark

Post subject:

Posted: Tue Sep 13, 2005 2:56 am

Senior

Joined: Thu Aug 25, 2005 3:35 am
Posts: 160

should you include the inner join fetch? Aren't you just selecting the count() and not any entities?

Top

nfung

Post subject: Yes, I just need row count.

Posted: Tue Sep 13, 2005 4:00 am

Regular

Joined: Mon May 30, 2005 11:20 pm
Posts: 66

Quote:

should you include the inner join fetch?

Sorry... dont quite follow you... I am using Inner join because I want to "eliminate" (exclude) those rows in parent table (inv_swatches) in which there's NO MATCHING record in child table (inv_item)

Quote:

Aren't you just selecting the count() and not any entities?

Yes! I just need row count and nothing else!

Thanks.

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum