Here is my problem :
I work with 2 tables (PO_POSTE and PO_POSTE_AGENT).
I can have many PO_POSTE_AGENT for one PO_POSTE but I can also have no PO_POSTE_AGENT for a PO_POSTE.
I'm trying to have a row for each PO_POSTE where there is 1 PO_POSTE_AGENT (or more) between two dates.
To make the request more simple, I removed the dates criteria but the exception remains.
If I translate this to SQL, it works.
So, where is the problem ?
Thank you.
Hibernate version: 2.1.2
Mapping documents:
Table PO_POSTE :
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping package="fr.civitas.rhcommun.po.metier.persistant">
<!--
*** Classe des postes
-->
<class
name="PoPostePersistant"
table="PO_POSTE"
schema="SIVITAS"
lazy="true"
>
<id name="pos_id" type="java.lang.Integer" column="POS_ID">
<generator class="hilo">
<param name="table">SIVITAS.PA_IDVALEUR</param>
<param name="column">PAV_ID</param>
</generator>
</id>
<property name="pos_num" type="java.lang.Integer" column="POS_NUM"/>
<property name="pos_dat_crea" type="java.sql.Date" column="POS_DAT_CREA"/>
<property name="pos_dat_prev" type="java.sql.Date" column="POS_DAT_PREV"/>
<property name="pos_dat_clot" type="java.sql.Date" column="POS_DAT_CLOT"/>
</class>
</hibernate-mapping>
Table PO_POSTE_AGENT :
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="fr.civitas.rhcommun.po.metier.persistant">
<!--
Classe des affectations de postes-agents
-->
<class
name="PoPosteAgentPersistant"
table="PO_POSTE_AGENT"
schema="SIVITAS"
lazy="true"
>
<id name="poa_id" type="java.lang.Integer" column="POA_ID">
<generator class="hilo">
<param name="table">SIVITAS.PA_IDVALEUR</param>
<param name="column">PAV_ID</param>
</generator>
</id>
<property name="poa_flg_affec_reelle" type="java.lang.Integer" column="POA_FLG_AFFEC_REELLE"/>
<property name="poa_typ_deb" type="java.lang.String" column="POA_TYP_DEB"/>
<property name="poa_dat_deb" type="java.sql.Date" column="POA_DAT_DEB"/>
<property name="poa_tau" type="java.lang.Double" column="POA_TAU"/>
<property name="poa_tps_tau" type="java.lang.String" column="POA_TPS_TAU"/>
<property name="poa_obs_deb" type="java.lang.String" column="POA_OBS_DEB"/>
<property name="poa_dat_fin" type="java.sql.Date" column="POA_DAT_FIN"/>
<property name="poa_flg_fin_reelle" type="java.lang.Integer" column="POA_FLG_FIN_REELLE"/>
<property name="poa_obs_fin" type="java.lang.String" column="POA_OBS_FIN"/>
<!-- associations -->
<many-to-one name="opc_poste" class="PoPostePersistant">
<column name="POS_ID" />
</many-to-one>
<many-to-one name="opc_motif_deb_affec" class="PoMotifPersistant">
<column name="PMO_ID_DEB_AFFEC" />
</many-to-one>
<many-to-one name="opc_motif_fin_affec" class="PoMotifPersistant">
<column name="PMO_ID_FIN_AFFEC" />
</many-to-one>
<many-to-one name="opc_agent" class="fr.civitas.commun.ag.metier.persistant.AgentPersistant">
<column name="AGT_ID" />
</many-to-one>
</class>
</hibernate-mapping>
Code:
SELECT DISTINCT poposte.pos_id,poposte.pos_num,poposte.pos_dat_crea
FROM PoPostePersistant AS poposte,
PoPosteAgentPersistant AS poposteagent
WHERE poposte.pos_dat_crea is not null
AND (SELECT COUNT(pa) FROM poposteagent as pa
WHERE pa.opc_poste.pos_id = poposte.pos_id) > 0
and poposteagent.opc_poste (+) = poposte
ORDER BY poposte.pos_num desc
Full stack trace of any exception that occurs:Code:
net.sf.hibernate.QueryException: unexpected token: as [SELECT DISTINCT poposte.pos_id,poposte.pos_num,poposte.pos_dat_crea FROM PoPostePersistant AS poposte,PoPosteAgentPersistant AS poposteagent WHERE poposte.pos_dat_crea is not null AND (SELECT COUNT(pa) FROM poposteagent as pa WHERE pa.opc_poste.pos_id = poposte.pos_id) > 0 and poposteagent.opc_poste (+) = poposte ORDER BY poposte.pos_num desc]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:94)
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:124)
at net.sf.hibernate.hql.WhereParser.token(WhereParser.java:212)
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:293)
at net.sf.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1602)
at net.sf.hibernate.impl.QueryImpl.scroll(QueryImpl.java:33)
at fr.civitas.technique.persistance.impl.PersistanceHibernateImpl.getQueryScroll(PersistanceHibernateImpl.java:547)
Name and version of the database you are using: Oracle 8
The generated SQL (show_sql=true):
No generated SQL
Debug level Hibernate log excerpt:
DEBUG
Login
FAQ
Search
