I've been able to make good use of the named queries and the Hibernate Query Language, but I need to get some data from a table using the 'distinct' keyword, so I guess I'm stuck using SQL. I've tried using sql-query in my mapping document, and it works if I do a very simple SQL statement (basically selecting all from a table) and even when I try to add a WHERE clause, but once I try to add in the 'distinct' portion it gives me the error:


[WARN] JDBCExceptionReporter - SQL Error: -99999, SQLState: 42703
[ERROR] JDBCExceptionReporter - An undefined column name was detected.
[WARN] JDBCExceptionReporter - SQL Error: -99999, SQLState: 42703
[ERROR] JDBCExceptionReporter - An undefined column name was detected.
net.sf.hibernate.exception.SQLGrammarException: error performing findBySQL
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3858)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.jegs.commerce.support.facade.PartSelector.getParentProductList(PartSelector.java:62)
at com.jegs.commerce.support.test.PartSelectorTest.testDrilldownParentLookup(PartSelectorTest.java:43)
at java.lang.reflect.Method.invoke(Native Method)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)
Caused by: java.sql.SQLException: An undefined column name was detected.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:382)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:359)
at com.ibm.as400.access.JDServerRow.findField(JDServerRow.java:293)
at com.ibm.as400.access.AS400JDBCResultSet.findColumn(AS400JDBCResultSet.java:514)
at com.ibm.as400.access.AS400JDBCResultSet.getInt(AS400JDBCResultSet.java:2912)
at net.sf.hibernate.type.IntegerType.get(IntegerType.java:21)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:427)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:200)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:281)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3855)
... 15 more




Here's my mapping document:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping package="com.jegs.commerce.support.data">
<class name="Drilldown" table="DRILLDOWN">
<id name="Ddparent" column="DDPARENT" type="integer">
<generator class="assigned"/>
</id>
<property
column="DDITEM"
length="10"
name="Dditem"
not-null="true"
type="integer"
/>
<property
column="DDPRODUCT"
length="64"
name="Ddproduct"
not-null="true"
type="string"
/>
<property
column="DDVAR08"
length="64"
name="Ddvar08"
not-null="true"
type="string"
/>
<property
column="DDVAR07"
length="64"
name="Ddvar07"
not-null="true"
type="string"
/>
<property
column="DDVAR06"
length="64"
name="Ddvar06"
not-null="true"
type="string"
/>
<property
column="DDVAR05"
length="64"
name="Ddvar05"
not-null="true"
type="string"
/>
<property
column="DDVAR04"
length="64"
name="Ddvar04"
not-null="true"
type="string"
/>
<property
column="DDVAR03"
length="64"
name="Ddvar03"
not-null="true"
type="string"
/>
<property
column="DDVAR02"
length="64"
name="Ddvar02"
not-null="true"
type="string"
/>
<property
column="DDVAR01"
length="64"
name="Ddvar01"
not-null="true"
type="string"
/>
</class>
<query name="AllRecords">
<![CDATA[from Drilldown]]>
</query>
<sql-query name="ParentList">
<return alias="drill" class="Drilldown"/>
<![CDATA[select distinct(DDVAR01) from WSJEGSCUSD.Drilldown {drill} WHERE DDPARENT = :parentId]]>
</sql-query>
</hibernate-mapping>

I've tried using both 'select distinct(DDVAR01)...' and 'select distinct({drill.Ddvar01})...' with the same results both ways. What am I missing?