[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6038
  • Last Modified:

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?


0
rpong
Asked:
rpong
  • 5
  • 2
1 Solution
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
Lee W, MVPTechnology 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
 
rpongAuthor Commented:
select distinct r from Release as r, ReleaseComponent as c where rownum <= 50.....
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now