Hello I have a problem with OJB (version 1.0.0) and alias definition in the SQL statement. I have two tables (T1 and T2) and some off the columns in these two tables have the same name. When I try to query the first table with an attribute from the second table I always get this error message from the DBMS
java.sql.SQLException: ORA-00918: column ambiguously defined The problem is that OJB doesn't use alias for the first table in the where clause. Here is an example for the statement: SELECT A0.COLUMN1,A0.COLUMN2,A0.COLUMN3,A0.COLUMN4 FROM T1 A0,T2 A1 WHERE A0.ID=A1.T1ID AND (( (COLUMN1 = '034') AND COLUMN2 = '78777') AND (A1.DATE >= '1900-12-12 12:00:00.0')) There are no alias definition for the columns COLUMN1 and COLUMN2 but these columns are defined in both tables. That's the reason why the ORA-00918 exception is thrown by the DBMS. So is there a way to force the OJB framework to use alias definitions in the where clause or does somebody have another solution to fix my problem? I would really appreciate it if somebody could help me. Cheers Christian Java code that makes the query: ====================================================== try { Criteria c = new Criteria(); // to set the alias on the criteria objects doesn't help // c.setAlias("a1"); c.addColumnEqualTo("COLUMN1","034"); c.addColumnEqualTo("COLUMN2","78777"); // Criteria c2 = new Criteria(); // c2.setAlias("a2"); // c2.addGreaterOrEqualThan("list.read",'1900-12-12 12:00:00.0'); c.addGreaterOrEqualThan("list.read",'1900-12-12 12:00:00.0'); // c.addAndCriteria(c2); QueryByCriteria query = new QueryByCriteria(A.class, c); Collection result = broker.getCollectionByQuery(query); return result; }catch (Exception e) { throw new MyException("text",e); }finally{ this.closeBroker(); } ======================================================= Mapping definition from the repository.xml: ======================================================= <!-- Definition for table T1 --> <class-descriptor class="A" table="T1"> <field-descriptor name="t1id" column="ID" jdbc-type="INTEGER" primarykey="true" autoincrement="true" /> <field-descriptor name="field1" column="COLUMN1" jdbc-type="VARCHAR" /> <field-descriptor name="field2" column="COLUMN2" jdbc-type="VARCHAR" /> <field-descriptor name="field3" column="COLUMN3" jdbc-type="VARCHAR" /> <field-descriptor name="field4" column="COLUMN4" jdbc-type="VARCHAR" /> <collection-descriptor name="list" element-class-ref="B" auto-retrieve="true" auto-update="none"> <inverse-foreignkey field-ref="t1id"/> </collection-descriptor> </class-descriptor> <!-- Definition for table T2 --> <class-descriptor class="B" table="T2"> <field-descriptor name="t2id" column="ID" jdbc-type="INTEGER" primarykey="true" autoincrement="true" /> <field-descriptor name="t1id" column="T1ID" jdbc-type="INTEGER" /> <field-descriptor name="field1" column="COLUMN1" jdbc-type="VARCHAR" /> <field-descriptor name="field2" column="COLUMN2" jdbc-type="VARCHAR" /> <field-descriptor name="read" column="DATE" jdbc-type="TIMESTAMP" /> <reference-descriptor name="t1" class-ref="A" auto-retrieve="true" auto-update="none"> <foreignkey field-ref="t1id" /> </reference-descriptor> </class-descriptor> =======================================================