Limit appended to SQL

I'm using HQL, which is Hibernate's SQL. I'm hoping it works

I have the following HQL

select distinct r from Release as r, ReleaseComponent as c
where r.deploymentDate between :fromDate and :toDate and r.name like :rName
and r.status like :rStatus and r.components.id = c.id and c.owner.userId like :cOwnerId
and c.externalId like :cExternalId limit 4

and I get an error of "unexpected token: limit "

Did i use this wrong? Is there a work around this LIMIT?


rpongAsked:
Who is Participating?
 
Lee W, MVPConnect With a Mentor Technology and Business Process AdvisorCommented:
As I said, I'm not familiar with HQL at all - but I did find this that might be somewhat helpful - note: 12.1

http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
LIMIT is used by MySQL - I'm not familiar with Hibernate SQL but it may simply not support limit.  MSSQL would use TOP - try this query instead:



select top 4 distinct r from Release as r, ReleaseComponent as c
where r.deploymentDate between :fromDate and :toDate and r.name like :rName
and r.status like :rStatus and r.components.id = c.id and c.owner.userId
like :cOwnerId
and c.externalId like :cExternalId
0
 
rpongAuthor Commented:
wow very quick, thanks. I'm trying it now and will post the error message. I mean the results
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
rpongAuthor Commented:
sorry I got side tracked created an evite
I got an error "unexpected token: 4"
it doesn't seem to recognize Top 4
0
 
rpongAuthor Commented:
Message
unexpected token: 4 near line 1, column 12 [select top 4 distinct r from com.att.ivr.drm.model.Release as r, com.att.ivr.drm.model.ReleaseComponent as c where r.deploymentDate between :fromDate and :toDate and r.name like :rName and r.status like :rStatus and r.components.id = c.id and c.owner.userId like :cOwnerId and c.externalId like :cExternalId]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 4 near line 1, column 12 [select top 4 distinct r from com.att.ivr.drm.model.Release as r, com.att.ivr.drm.model.ReleaseComponent as c where r.deploymentDate between :fromDate and :toDate and r.name like :rName and r.status like :rStatus and r.components.id = c.id and c.owner.userId like :cOwnerId and c.externalId like :cExternalId]
Cause
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 4 near line 1, column 12 [select top 4 distinct r from com.att.ivr.drm.model.Release as r, com.att.ivr.drm.model.ReleaseComponent as c where r.deploymentDate between :fromDate and :toDate and r.name like :rName and r.status like :rStatus and r.components.id = c.id and c.owner.userId like :cOwnerId and c.externalId like :cExternalId]
0
 
rpongAuthor Commented:
someone suggested Rownum.
I don't know SQL so i'll have to look this up
0
 
rpongAuthor Commented:
select distinct r from Release as r, ReleaseComponent as c where rownum <= 50.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.