asked on

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?

Lee W, MVP

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

rpong

ASKER

wow very quick, thanks. I'm trying it now and will post the error message. I mean the results

rpong

ASKER

sorry I got side tracked created an evite
I got an error "unexpected token: 4"
it doesn't seem to recognize Top 4

rpong

ASKER

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]

rpong

ASKER

someone suggested Rownum.
I don't know SQL so i'll have to look this up

ASKER CERTIFIED SOLUTION

Lee W, MVP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rpong

ASKER

select distinct r from Release as r, ReleaseComponent as c where rownum <= 50.....