Hello All...

while using the SubQueries in hibernate i got this error...
can anyone help me out in getting this cleared!
thanx a bunch!

Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax near 'select inventory1_.PS_ID from clts_inventory inventory1_ where (inventory1_.STOR' at line 1"

Hibernate Query is :

Hibernate: select productspe0_.PS_DESCRIPTION as x0_0_ from clts_prdt_spec productspe0_ where (productspe0_.PS_DESCRIPTION like 'c%' )and(productspe0_.PS_ID in(select inventory1_.PS_ID from clts_inventory inventory1_ where (inventory1_.STORE_ID=303 )))

My Query is :

select ProductSpecification.ProductDescription from productspecification.domain.ProductSpecification ProductSpecification where ProductSpecification.ProductDescription like 'c%' and ProductSpecification.ProductSpecificationId in (select Inventory.ProductSpecification.ProductSpecificationId from inventory.domain.Inventory Inventory where Inventory.Store.StoreId=303)

Tried using this query also...:

Hibernate Query is :

Hibernate: select productspe0_.PS_DESCRIPTION as x0_0_ from clts_prdt_spec productspe0_, clts_inventory inventory1_ where (productspe0_.PS_DESCRIPTION like 'c%' )and(productspe0_.PS_ID in(select inventory2_.PS_ID from clts_inventory inventory2_ where (inventory2_.STORE_ID=303 )))

My Query is :

select ProductSpecification.ProductDescription from productspecification.domain.ProductSpecification ProductSpecification,
inventory.domain.Inventory Inventory
where ProductSpecification.ProductDescription like 'c%' and ProductSpecification.ProductSpecificationId in (select Inventory.ProductSpecification.ProductSpecificationId from inventory.domain.Inventory Inventory where Inventory.Store.StoreId=303)

am i wrong in my query?...or its in accessing the Data???

-Munas.

dear all....

i didnt get any solution....but i tried something like this...

select ProductSpecification.ProductDescription from productspecification.domain.ProductSpecification ProductSpecification where ProductSpecification.ProductDescription like 'p%' and ProductSpecification.ProductSpecificationId in (101,103,104)

this query works fine....but if i substitute the IN cluase () with other select query(select ID from Store), it gives the error...i m using MySql as Database....!

tips on this will be extremely appreciated!
thanx a bunch!

-Munas.

Are you using a mysql version that supports subselects?

Dear Michael..

My MySql is not SubQuery Supported version...
i thought Hibernate will make it easy ...!

so i have to use Tow Queries to get the desired o/p...like...
1. get all the Product.Id & Descriptions
2. get all the product ids from Store and
make it as a string like (123,234,345...)
then compare both!

is there any other alternative solution for this (using singe HQL)?

-Munas.