You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Lauber Markus, Bedag" <Ma...@bedag.ch> on 2006/02/23 09:51:05 UTC
Problem joining two tables with same column name
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>
=======================================================
Re: Problem joining two tables with same column name
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi markus,
a column is not prefixed with an alias when ojb cannot find an
appropriate alias / classdescriptor for the attribute. there have been
some improvements in this area, so please try the current ojb 1.0.4.
hth
jakob
btw: is ojb now 'the orm-tool' used in bedag ?
Lauber Markus, Bedag schrieb:
> 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>
> =======================================================
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Problem joining two tables with same column name
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi neha,
plase post the relevant part of the repository and the generated sql.
jakob
neha wrote:
>
> Hi,
> Does anyone have the solution for this?
>
> Do mail to the following id
> neha_garg123@yahoo.com
>
> Thanks
> Neha
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Problem joining two tables with same column name
Posted by neha <ne...@yahoo.com>.
Hi,
Does anyone have the solution for this?
Do mail to the following id
neha_garg123@yahoo.com
Thanks
Neha
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org