Article or help on doing "select unique..." with H

erizzo · **Joined:** Wed Oct 26, 2005 11:55 pm **Posts:** 14

I've searched the hibernate.org site and done some googling for info, but haven't found anything useful yet.

I'm trying to do a basic "select unique myProperty from myEntity" in HQL but am not having any luck. I got unexpected token errors with that form, which I can get past if I do it as "select unique(myProperty) from myEntity". However, that form produces "No data type for node: org.hibernate.hql.ast.MethodNode" exceptions.

Since this is a common SQL operation I figured HQL would support it. Am I wrong? If so, is there any documentation about the justification for not suporting it (for example, "It is planned for 3.1", etc.)

I am willing to do research and reading, but have been unable to find any examples or material about this kind of query (which surprises me since it seems like something that would be a fairly common need)
Do I need to drop down to a SQLQuery to accomplish this? I hope not because I'd like to keep the nice mapping features of HQL...

Any pointers or advice is much appreciated.

TIA,
Eric

Hibernate version: 3.0.5

My HQL query string:

Code:

select unique(map.origin) from afriterra.domain.AntiqueMap map

Full stack trace of any exception that occurs:

Code:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'unique' {originalText=unique}
    \-[EXPR_LIST] SqlNode: 'exprList'
       \-[DOT] DotNode: 'antiquemap0_.origin' {propertyName=origin,dereferenceType=4,propertyPath=origin,path=map.origin,tableAlias=antiquemap0_,className=afriterra.domain.AntiqueMap,classAlias=map}
          +-[ALIAS_REF] IdentNode: 'antiquemap0_.ID' {alias=map, className=afriterra.domain.AntiqueMap, tableAlias=antiquemap0_}
          \-[IDENT] IdentNode: 'origin' {originalText=origin}

   at org.hibernate.hql.ast.SelectClause.initializeExplicitSelectClause(SelectClause.java:136)
   at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:440)
   at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:351)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.afterQuery(HqlSqlBaseWalker.java:126)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:471)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:201)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:151)
   at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
   at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
   at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

[/code]

Siegbert · **Joined:** Mon Jan 05, 2004 11:04 am **Posts:** 16

If you only want to select distinct values then try

Code:

select distinct map.origin from afriterra.domain.AntiqueMap map

If you want to generate a unique constraint on the generated schema
then use the unique="true" option on the property
(see 6.1.9 Reference)

erizzo · **Joined:** Wed Oct 26, 2005 11:55 pm **Posts:** 14

[quote="Siegbert"]If you only want to select distinct values then try

Code:

select distinct map.origin from afriterra.domain.AntiqueMap map

Wow, I'm feeling a little silly. I was focued on the "unique" keyword and completely forgot that SQL uses "distinct" and hence so would HQL, logically.

That explains why my Googling was coming up short.

Thanks - credit awarded.

Eric