• 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: 2369
25
  • 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: 10763
148
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
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!