Having issues tring to convert a SQL query into the Hibernate Query. Please excuse anything I have missed, for I will quickly post the missing information.

Any help is greatly appreciated. Overall thank you for taking the time tolook at this issue.

Paul.


SQL Query:
SELECT COUNT(dbo.VinMakeLU.strVinMake), dbo.VinMakeLU.strVinMake
FROM dbo.DealershipVins INNER JOIN
dbo.VinLU ON dbo.DealershipVins.fkVinID = dbo.VinLU.pkVinID INNER JOIN
dbo.VinMakeLU ON dbo.VinLU.fkVinMakeID = dbo.VinMakeLU.pkVinMakeID
GROUP BY dbo.DealershipVins.fkAccountID, dbo.VinMakeLU.strVinMake
HAVING (dbo.DealershipVins.fkAccountID = 10044)

Hibernate Query:
select count(vinMakeProfile), vinMakeProfile.vinMakeName from VinProfile inner join VehicleProfile inner join VinMakeProfile vinMakeProfile group by VehicleProfile.id, vinMakeProfile.vinMakeName having VinProfile.autocheckDealershipProfile.id=10044




Hibernate version: 2.17

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping>
<class name="com.test.ac.model.VinProfile" table="DealershipVins">
<composite-id>
<key-many-to-one name="acDealershipProfile" class="com.test.ac.model.acDealershipProfile" column="fkAccountID"/>
<key-many-to-one name="vehicleProfile" class="com.test.ac.model.VehicleProfile" column="fkVinID"/>
</composite-id>
<many-to-one update="false" insert="false" name="acDealershipProfile" column="fkAccountID" class="com.test.ac.model.acDealershipProfile" not-null="true"/>
<many-to-one update="false" insert="false" name="vehicleProfile" column="fkVinID" class="com.test.ac.model.VehicleProfile" not-null="true"/>
<property name="populID" type="integer">
<column name="intPopulID" sql-type="INTEGER" />
</property>
<property name="vinStockNumber" type="string">
<column name="strVinStockNmbr" sql-type="varchar" />
</property>
<property name="vinOneOff" type="boolean">
<column name="btVinOneOff" sql-type="bit" />
</property>
<property name="sold" type="boolean">
<column name="btSold" sql-type="bit" />
</property>
<property name="lastViewedDate" type="timestamp">
<column name="dteVDate" sql-type="TIMESTAMP" />
</property>
<property name="createDate" type="timestamp">
<column name="dteIDate" sql-type="TIMESTAMP" />
</property>
<property name="updateDate" type="timestamp">
<column name="dteUDate" sql-type="TIMESTAMP" />
</property>
</class>
<query name ="findVinProfileByVinIDAndAccountID">
<![CDATA[
from VinProfile as VinProfile where VinProfile.acDealershipProfile.id = :dpid and
VinProfile.vehicleProfile.id = :vhid
]]>
</query>
<query name ="findVinProfileByAccountID">
<![CDATA[
from VinProfile as VinProfile where VinProfile.acDealershipProfile.id = :dpid
]]>
</query>
<query name ="findVinProfilesUpdatedToday">
<![CDATA[
from VinProfile as VinProfile where CONVERT(CHAR(10),VinProfile.updateDate,120)=CONVERT(CHAR(10),getDate(),120)
]]>
</query>
<query name ="findVinProfilesUpdatedTodayByAccountID">
<![CDATA[
from VinProfile as VinProfile where CONVERT(CHAR(10),VinProfile.updateDate,120)=CONVERT(CHAR(10),getDate(),120) and
VinProfile.acDealershipProfile.id = :dpid
]]>
</query>
<query name ="findVinProfilesInsertedInLast30Days">
<![CDATA[
from VinProfile as VinProfile where CONVERT(CHAR(10),VinProfile.createDate,120)> CONVERT(CHAR(10),getDate()-30,120)
]]>
</query>
<query name ="findVinProfilesInsertedInLast30DaysByAccountID">
<![CDATA[
from VinProfile as VinProfile where CONVERT(CHAR(10),VinProfile.createDate,120)> CONVERT(CHAR(10),getDate()-30,120) and
VinProfile.acDealershipProfile.id = :dpid
]]>
</query>
<query name ="findVinProfileByInsertDate">
<![CDATA[
from VinProfile as vinProfile where CONVERT(CHAR(10),vinProfile.createDate,120)= :vndt and
vinProfile.acDealershipProfile.id = :dpid
]]>
</query>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping>
<class name="com.test.ac.model.VehicleProfile" table="VinLU">
<id name="id" type="integer">
<column name="pkVinID" sql-type="INTEGER" not-null="true"/>
<generator class="native"/>
</id>

<many-to-one name="vinMakeProfile" column="fkVinMakeID" class="com.test.ac.model.VinMakeProfile" not-null="true" />
<many-to-one name="vinModelProfile" column="fkVinModelID" class="com.test.ac.model.VinModelProfile" not-null="true" />

<property name="vinYear" type="integer">
<column name="intVinYear" sql-type="INTEGER" />
</property>

<property name="vinNumber" type="string">
<column name="strVinNmbr" sql-type="varchar" />
</property>

<property name="vinXML" type="string">
<column name="strVinXML" sql-type="text" />
</property>

<property name="vinProblem" type="boolean">
<column name="btVinProblem" sql-type="bit" />
</property>

<property name="vinWholeSale" type="boolean">
<column name="btVinWholeSale" sql-type="bit" />
</property>

<property name="vinRetail" type="boolean">
<column name="btVinRetail" sql-type="bit" />
</property>


<property name="createDate" type="timestamp">
<column name="dteIDate" sql-type="TIMESTAMP" />
</property>

<property name="updateDate" type="timestamp">
<column name="dteUDate" sql-type="TIMESTAMP" />
</property>


</class>
<query name ="findVechicleProfileByVinID">
<![CDATA[
from VehicleProfile as vehicleProfile where vehicleProfile.id = :id
]]>
</query>

<query name ="findVechicleProfileByVinNumber">
<![CDATA[
from VehicleProfile as vehicleProfile where vehicleProfile.vinNumber = :vinNumber
]]>
</query>

<query name ="findVehicleProfilesUpdatedToday">
<![CDATA[
from VehicleProfile as vehicleProfile where CONVERT(CHAR(10),vehicleProfile.updateDate,120)=CONVERT(CHAR(10),getDate(),120)
]]>
</query>

<query name ="findVehicleProfilesInsertedInLast30Days">
<![CDATA[
from VehicleProfile as vehicleProfile where CONVERT(CHAR(10), vehicleProfile.createDate,120)> CONVERT(CHAR(10),getDate()-30,120)
]]>
</query>

</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping>
<class name="com.test.ac.model.VinMakeProfile" table="VinMakeLU">

<id name="id" type="integer">
<column name="pkVinMakeID" sql-type="INTEGER" not-null="true"/>
<generator class="native"/>
</id>
<property name="vinMakeName" type="string">
<column name="strVinMake" sql-type="varchar" />
</property>

</class>

<query name ="findVinMakeProfileByvinMakeID">
<![CDATA[
from VinMakeProfile as vinMakeProfile where vinMakeProfile.id = :id
]]>
</query>
<query name ="findVinMakeProfileByvinMake">
<![CDATA[
from VinMakeProfile as vinMakeProfile where vinMakeProfile.vinMakeName = :vinMakeName
]]>
</query>

<query name ="findAllVinMakeProfiles">
<![CDATA[
from VinMakeProfile as vinMakeProfile order by vinMakeProfile.vinMakeName asc
]]>
</query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
public static List queryTest()throws PersistenceException
{
List retValue = null;
Session session = null;
Connection con = null;

String strSQL = "select count(vinMakeProfile), vinMakeProfile.vinMakeName from VinProfile " +
"inner join VehicleProfile inner join VinMakeProfile vinMakeProfile " +
"group by VehicleProfile.id, vinMakeProfile.vinMakeName " +
"having VinProfile.autocheckDealershipProfile.id=10044 ";

try
{
con = ConnectionHelper.getACEDBConnection();
if(con == null) { throw new PersistenceException("Connection is null for ACEDB"); }
session = HibernateUtil.getSession(con);
Query query = session.createQuery(strSQL);

retValue = query.list();
}
catch(HibernateException he)
{ throw new PersistenceException("Error in findAllVinMakeProfiles of VinMakeProfileService ",he); }
finally
{
try { if(session != null && session.isOpen()) { session.close(); } }
catch(HibernateException he){}

try { if (con != null && !con.isClosed()) { con.close(); } }
catch(SQLException sqle){}
}

System.out.println("COMPLETED");

return retValue;

}


Full stack trace of any exception that occurs:
net.sf.hibernate.QueryException: outer or full join must be followed by path expression [select count(vinMakeProfile), vinMakeProfile.vinMakeName from com.test.ac.model.VinProfile inner join VehicleProfile inner join VinMakeProfile vinMakeProfile group by VehicleProfile.id, vinMakeProfile.vinMakeName having VinProfile.acDealershipProfile.id=10044 ]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:166)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.test.ac.service.VinMakeProfileService.queryTest(VinMakeProfileService.java:150)
at com.test.ac.service.VinMakeProfileService.main(VinMakeProfileService.java:224)
rethrown as com.test.persistence.PersistenceException: Error in findAllVinMakeProfiles of VinMakeProfileService : outer or full join must be followed by path expression [select count(vinMakeProfile), vinMakeProfile.vinMakeName from com.test.ac.model.VinProfile inner join VehicleProfile inner join VinMakeProfile vinMakeProfile group by VehicleProfile.id, vinMakeProfile.vinMakeName having VinProfile.acDealershipProfile.id=10044 ]
at com.test.ac.service.VinMakeProfileService.queryTest(VinMakeProfileService.java:153)
at com.test.ac.service.VinMakeProfileService.main(VinMakeProfileService.java:224)
Caused by: net.sf.hibernate.QueryException: outer or full join must be followed by path expression [select count(vinMakeProfile), vinMakeProfile.vinMakeName from com.test.ac.model.VinProfile inner join VehicleProfile inner join VinMakeProfile vinMakeProfile group by VehicleProfile.id, vinMakeProfile.vinMakeName having VinProfile.acDealershipProfile.id=10044 ]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:166)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.test.ac.service.VinMakeProfileService.queryTest(VinMakeProfileService.java:150)
... 1 more


Name and version of the database you are using:
Microsoft SQL Server 2000 Enterprise.