Hi,

I'm having this strange annoying problem:
Hibernate is generating a query for me that gives the ORA-00918: column ambiguously defined error.

What i have is the following : in my legacy database there exist two types of modules, Application Modules (Applmod) and Communicator Modules (Communicator).

The schema i have build makes uses of the abstract Module class that has the shared properties for both types of module.
There is an entity ApplicationType, that i need to reference from another table. The problem there is that the foreign key in the table for that AType is not entirely listed in the modules. I can only get part of it by a forumla of substring. The other two parts of the foreign-key on the other hand, are fixed , but different for both types.

Is there a way to either specify to Hibernate that it should use aliases for my joins, or is there a solution for my mapping file ?

Below you can find the necessary mapping files and the likes.
I can't find ANY ideas on the net (and trust me, i have been looking for quite some time now)

Thanks !

Jeroen



I have build a

Hibernate version:
3.2.0.cr4

Mapping documents:

<hibernate-mapping>
<class abstract="true"
name="com.watchtalk.web.businessobject.Module">
<id column="APPLMODID" length="7" name="applmodid">
<generator class="assigned" />
</id>

<many-to-one name="opsmode"
class="com.watchtalk.web.businessobject.OperationalMode"
update="false" insert="false">
<column name="OPSMODE" length="2" />
<formula>'OPSMODE'</formula>
<formula>'0'</formula>
</many-to-one>

<!-- many-to-one name="mtype"
class="com.watchtalk.web.businessobject.ModuleType" update="false"
insert="false">
<formula>SUBSTR(APPLMODID, 3, 2)</formula>
<formula>'MTYPE'</formula>
<formula>SUBSTR(APPLMODID, 1, 2)</formula>
</many-to-one -->

<property formula="SUBSTR(APPLMODID, 3, 2)" generated="never"
lazy="false" length="2" name="moduleName" />
<property formula="SUBSTR(APPLMODID, 5, 2)" generated="never"
lazy="false" length="2" name="moduleVersion" />
<property formula="SUBSTR(APPLMODID, 7, 8)" generated="never"
lazy="false" length="8" name="productName" />
<property column="STATUS" generated="never" lazy="false"
length="3" name="status" />
<property column="LASTALIVE" generated="never" lazy="false"
length="7" name="lastalive" />
<property column="COMMID" generated="never" lazy="false"
length="6" name="commid" not-null="true" />
<property column="POSTOFFICE" generated="never" lazy="false"
length="64" name="postoffice" not-null="true" />
<property column="BLACKLIST" generated="never" lazy="false"
length="22" name="blacklist" />

<union-subclass name="com.watchtalk.web.businessobject.Applmod"
table="APPLMOD">

<many-to-one name="atype" outer-join="false"
class="com.watchtalk.web.businessobject.ApplicationType"
update="false" insert="false">
<formula>SUBSTR(APPLMODID, 1, 2)</formula>
<formula>'ATYPE'</formula>
<formula>'41'</formula>
</many-to-one>

<property formula="SUBSTR(APPLMODID, 3, 2)"
generated="never" lazy="false" length="2" name="moduleName" />

<property column="FULLCOMMID" generated="never" lazy="false"
length="7" name="fullcommid" not-null="true" />
<set lazy="true" name="metingen"
order-by="METINGTIJD desc">
<key column="applmodid" />
<one-to-many
class="com.watchtalk.web.businessobject.Meting" />
</set>
<set name="aspList" schema="METR_DATA" table="GROUPS"
lazy="false">
<key column="APPLMODID" />
<composite-element
class="com.watchtalk.web.businessobject.ASP_Group">
<parent name="module" />
<many-to-one name="asp" column="ASP_ID"
not-null="true" class="com.watchtalk.web.businessobject.Asp" />
<property name="aspCode" column="asp_Code" />
</composite-element>
</set>
</union-subclass>
<union-subclass
name="com.watchtalk.web.businessobject.Communicator"
table="COMMUNICATOR">

<many-to-one name="atype" outer-join="false"
class="com.watchtalk.web.businessobject.ApplicationType"
update="false" insert="false">
<formula>SUBSTR(APPLMODID, 1, 2)</formula>
<formula>'ATYPE'</formula>
<formula>'0'</formula>
</many-to-one>

<one-to-one name="locatie"
class="com.watchtalk.web.businessobject.Locatie">
<formula>SUBSTR(APPLMODID, 3, 12)</formula>
</one-to-one>
<set lazy="true" name="modules">
<key column="fullcommid" />
<one-to-many
class="com.watchtalk.web.businessobject.Applmod" />
</set>
<many-to-one name="connectProfile" column="PROFILE_ID"
class="com.watchtalk.web.businessobject.ConnectProfile"
update="true" insert="false" lazy="false" />
<many-to-one name="configProfile" column="CONFIG_ID"
class="com.watchtalk.web.businessobject.ConfigProfile" update="true"
insert="false" lazy="false" />
<property column="FIRSTALIVE" generated="never" lazy="false"
length="7" name="firstalive" />
<property column="TIME2SEND" generated="never" lazy="false"
length="3" name="time2send" />
<property column="VERSION" generated="never" lazy="false"
length="3" name="version" />
</union-subclass>
</class>
</hibernate-mapping>

Full stack trace of any exception that occurs:

javax.el.ELException: /pages/modulesoverview.xhtml @12,92 rendered="#{empty(moduleBean.modules)}": org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select this_.APPLMODID as APPLMODID0_3_, this_.OPSMODE as OPSMODE0_3_, this_.STATUS as STATUS0_3_, this_.LASTALIVE as LASTALIVE0_3_, this_.COMMID as COMMID0_3_, this_.POSTOFFICE as POSTOFFICE0_3_, this_.BLACKLIST as BLACKLIST0_3_, this_.FULLCOMMID as FULLCOMMID1_3_, this_.PROFILE_ID as PROFILE1_3_3_, this_.CONFIG_ID as CONFIG2_3_3_, this_.FIRSTALIVE as FIRSTALIVE3_3_, this_.TIME2SEND as TIME4_3_3_, this_.VERSION as VERSION3_3_, 'OPSMODE' as formula0_3_, '0' as formula1_3_, SUBSTR(this_.APPLMODID, 3, 2) as formula2_3_, SUBSTR(this_.APPLMODID, 5, 2) as formula3_3_, SUBSTR(this_.APPLMODID, 7, 8) as formula4_3_, SUBSTR(this_.APPLMODID, 1, 2) as formula5_3_, 'ATYPE' as formula6_3_, '41' as formula7_3_, SUBSTR(this_.APPLMODID, 3, 2) as formula8_3_, SUBSTR(this_.APPLMODID, 1, 2) as formula9_3_, 'ATYPE' as formula10_3_, '0' as formula11_3_, SUBSTR(this_.APPLMODID, 3, 12) as formula12_3_, this_.clazz_ as clazz_3_, applicatio1_.id as id4_0_, applicatio1_.type as type4_0_, applicatio1_.PARENT_ID as PARENT3_4_0_, applicatio1_.DESCRIPTION as DESCRIPT4_4_0_, applicatio1_.id as id4_1_, applicatio1_.type as type4_1_, applicatio1_.PARENT_ID as PARENT3_4_1_, applicatio1_.DESCRIPTION as DESCRIPT4_4_1_, locatie5_.COMMID as COMMID10_2_, locatie5_.STRAAT as STRAAT10_2_, locatie5_.HUISNR as HUISNR10_2_, locatie5_.GEMEENTE as GEMEENTE10_2_, locatie5_.POSTCODE as POSTCODE10_2_, locatie5_.LAND as LAND10_2_, locatie5_.MEMO as MEMO10_2_ from ( select OPSMODE, null as TIME2SEND, STATUS, null as PROFILE_ID, POSTOFFICE, null as VERSION, FULLCOMMID, APPLMODID, COMMID, null as CONFIG_ID, null as FIRSTALIVE, LASTALIVE, BLACKLIST, 1 as clazz_ from APPLMOD union all select OPSMODE, TIME2SEND, STATUS, PROFILE_ID, POSTOFFICE, VERSION, null as FULLCOMMID, APPLMODID, COMMID, CONFIG_ID, FIRSTALIVE, LASTALIVE, BLACKLIST, 2 as clazz_ from COMMUNICATOR ) this_ inner join COMS_DATA.PARAMETER applicatio1_ on SUBSTR(this_.APPLMODID, 1, 2)=applicatio1_.id and 'ATYPE'=applicatio1_.type and '41'=applicatio1_.PARENT_ID inner join COMS_DATA.PARAMETER applicatio1_ on SUBSTR(this_.APPLMODID, 1, 2)=applicatio1_.id and 'ATYPE'=applicatio1_.type and '0'=applicatio1_.PARENT_ID left outer join COMS_DATA.LOCATIE locatie5_ on SUBSTR(this_.APPLMODID, 3, 12)=locatie5_.COMMID where (1=1) and applicatio1_.id=? and applicatio1_.PARENT_ID=?]; nested exception is java.sql.SQLException: ORA-00918: column ambiguously defined

Name and version of the database you are using:
Oracle 9.2

you'd better go to the regular user forum