AW: Problem joining two tables with same column name

[Lauber Markus, Bedag]

Hi Jakob

Your last tip (use addEqualto() instead of addColumnEqualTo()) solved my
problem. 

Thank you very much for your help.

Cheers 
Markus

AW: Problem joining two tables with same column name

[Lauber Markus, Bedag]

Hi Jakob

Thank you for the explanation. When will OJB 1.0.5 be released? 

What do you suggest, is it better to use the method addEqualTo() or
addColumnEqualTo() in a project? In our project we used both methods and
we want to refactor our code.

Greetings
Markus 

AW: Problem joining two tables with same column name

[Lauber Markus, Bedag]

Hi Jakob

I switched to the new version of OJB (1.0.4). But the new release
brought no improvement in the generation of the SQL statement. It still
has no alias prefixes for the first table in the where clause. 

 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'))

Greetings
Markus

Problem joining two tables with same column name

[Lauber Markus, Bedag]

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
===