Hi all,
Query: select distinct(item.id) from Item item LEFT JOIN Stock stock ON item.id=stock.itemId

unexpected token: on near line 1

Tables Item and Stock have no relation..

Possible ways to get result in hql query or criteria. Please need immediately solution.


Regards,
K.Babu

You can't join in HQL without mapping. The keyword "on" is native query and not recognized by HQL.

The example seems like you can just do a simple selection (select item.id from Item item or select stock.itemId from Stock stock). If you just simplified the example and you need all of the items even if there is no associated Stock I believe you can do this with a right outer join. This should give you all items from item and give you the "other" value from Stock in the cases where there is a match. This will give you a list of object arrays where the first element will be item.id and the second will be stock.other. The stock.other value will be null for items with no associate stock. (I added the "other" to the selection assuming you need something from stock, otherwise there is no reason to join, just select directly from Item.)

select distinct(item.id), stock.other from Stock stock right outer join stock.itemId

Just for the record, I am no expert but I hope this information helps.

Hi JHaberlin,
First i would like thank you to response for this hard problem.

When execute the queries i get "java.lang.NullPointerException"

"select distinct(item.id), stock.id from Stock stock right outer join stock.itemId"
(Or)
"select distinct(itemId), stock.id from Stock stock right outer join stock.itemId"

Suggest me to overcome from "java.lang.NullPointerException"...



For reference use these hbm:
<hibernate-mapping>
<class name="com.ideamed.store.model.Stock" table="STORE_STOCK" dynamic-update="true" select-before-update="true">
<id name="id" column="ID" type="java.lang.Integer">
<generator class="increment" />
</id>
<property name="deptId" type="string" column="DEPT_ID" />
<property name="itemId" type="string" column="ITEM_ID" />
<property name="currentStock" type="integer" column="CURRENT_STOCK" />
</class>
</hibernate-mapping>

<hibernate-mapping>
<joined-subclass entity-name="Item" extends="Data"
table="RT_DATA_ITEM" dynamic-update="true" dynamic-insert="true">
<key column="ID"/>
<property name="aditmName" type="string" column="ADITMNAME" />
<property name="aditmMaxStock" type="string" column="ADITMMAXSTOCK" />
<property name="itemStrength" type="string" column="ITEMSTRENGTH" />
<property name="itemMolecule" type="string" column="ITEMMOLECULE" />
<property name="aditmUnit" type="string" column="ADITMUNIT" />
<property name="aditmMinStock" type="string" column="ADITMMINSTOCK" />
<property name="isBatchNoNeed" type="string" column="ISBATCHNONEED" />
<property name="aditmReOrdLevel" type="string" column="ADITMREORDLEVEL" />
</joined-subclass>
</hibernate-mapping>

Thanks,
K.Babu