• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Issue with count(*) query in HQL

 
Varun sat
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ..
I am having an issue with HQL. If any of you guys could help
here is the query -
select count( * ) from ( select products.productID,products.description,nvl(products.conditionalOrderReasonCode,reasonCodes.conditionalOrderabilityCode) as conditionalOrderReasonCode,products.prePaidMonths from com.ncr.catalog.dataobject.Product products,com.ncr.catalog.dataobject.ProductGlobal productsGlobal,com.ncr.catalog.dataobject.ProductRelease productRelease, com.ncr.catalog.dataobject.ReleaseConOrderReason reasonCodes where ( ( products.productID = productsGlobal.productID and productsGlobal.releasesRef=productRelease.backpointerCollectionGID and productRelease.condOrderReasonRef=reasonCodes.backpointerCollectionGID and (products.conditionalOrderReasonCode is not null or reasonCodes.conditionalOrderabilityCode='CO')) AND products.countryCode='US') AND ( (products.productID LIKE 'G%' ) ) )

Output in application -
21:25:27.986 - line 1:24: unexpected token: (

I think I am closing all brackets properly.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It doesn't like the subquery.
You don't need that subquery anyway.

Something like:

should do. Though double check the brackets as I have no idea if they're in the right place or even match. They look excessive to me.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Varun sat,

First of all, a warm welcome to CodeRanch!

Varun sat wrote:I think I am closing all brackets properly.

You have a HQL query and you are using the NVL function which is (as far as I know) not supported in JPQL or HQL. Furthermore, if you are only interested in a count of the records, I wonder why you provide a list of columns in a subquery Like Dave already has mentioned, the subquery is not needed in your use case. And you should get rid of all unnecessary brackets (e.g. in the following condition ( (products.productID LIKE 'G%' ) ) the brackets have no added value at all.

Hope it helps!
Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic