Re: Problem joining two tables with same column name
Hi, Does anyone have the solution for this? Do mail to the following id [EMAIL PROTECTED] Thanks Neha - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: AW: Problem joining two tables with same column name
Hi Jakob, do you think that I have the same problem? It appears only with m-n. ## ### 1. mapping ### ## !-- m - n -- collection-descriptor name=table_n collection-class=org.apache.ojb.broker.util.collections.ManageableArrayList element-class-ref=de.on_ergy.lakon.data.model.TableN auto-retrieve=true auto-update=object auto-delete=link proxy=true indirection-table=table_m_table_n fk-pointing-to-this-class column=table_m_obj_id/ fk-pointing-to-element-class column=table_n_obj_id/ /collection-descriptor # ### 2. javacode for query ### # crit.addEqualTo(table_n.obj_id, 1); # ### 3. ojb sql output ### # SELECT A0.obj_id, FROM table_m A0 LEFT OUTER JOIN ( table_m_table_n A1 LEFT OUTER JOIN table_n A2 ON A1.table_n_obj_id=A2.obj_id ) ON A0.obj_id=A1.table_m_obj_id WHERE obj_id = 1 ## ### 4. the bug ### ## Now, the problem is the incorrect obj_id without A2 as prefix :o(. So, I get the following logical exception: org.postgresql.util.PSQLException: ERROR: column reference obj_id is ambiguous Thanks a lot! Josef Wagner Original-Nachricht Betreff:Re: AW: Problem joining two tables with same column name Datum: Fri, 03 Mar 2006 19:03:50 +0100 Von:Jakob Braeuchi [EMAIL PROTECTED] Antwort an: OJB Users List ojb-user@db.apache.org An: OJB Users List ojb-user@db.apache.org, [EMAIL PROTECTED] Referenzen: [EMAIL PROTECTED] hi markus, ojb 1.0.5 should be released this month. you should always use 'attributes' not 'columns' in your queries and let ojb translate the attribut-name into a column-name. hth jakob ps: what do you use ojb for in bedag ? Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: AW: Problem joining two tables with same column name
hi josef, you should not prefix the attribute with the tablename. crit.addEqualTo(id) is ok, where id is the name of the attribute not the name of the column. hth jakob Josef Wagner schrieb: Hi Jakob, do you think that I have the same problem? It appears only with m-n. ## ### 1. mapping ### ## !-- m - n -- collection-descriptor name=table_n collection-class=org.apache.ojb.broker.util.collections.ManageableArrayList element-class-ref=de.on_ergy.lakon.data.model.TableN auto-retrieve=true auto-update=object auto-delete=link proxy=true indirection-table=table_m_table_n fk-pointing-to-this-class column=table_m_obj_id/ fk-pointing-to-element-class column=table_n_obj_id/ /collection-descriptor # ### 2. javacode for query ### # crit.addEqualTo(table_n.obj_id, 1); # ### 3. ojb sql output ### # SELECT A0.obj_id, FROM table_m A0 LEFT OUTER JOIN ( table_m_table_n A1 LEFT OUTER JOIN table_n A2 ON A1.table_n_obj_id=A2.obj_id ) ON A0.obj_id=A1.table_m_obj_id WHERE obj_id = 1 ## ### 4. the bug ### ## Now, the problem is the incorrect obj_id without A2 as prefix :o(. So, I get the following logical exception: org.postgresql.util.PSQLException: ERROR: column reference obj_id is ambiguous Thanks a lot! Josef Wagner Original-Nachricht Betreff:Re: AW: Problem joining two tables with same column name Datum: Fri, 03 Mar 2006 19:03:50 +0100 Von:Jakob Braeuchi [EMAIL PROTECTED] Antwort an: OJB Users List ojb-user@db.apache.org An: OJB Users List ojb-user@db.apache.org, [EMAIL PROTECTED] Referenzen: [EMAIL PROTECTED] hi markus, ojb 1.0.5 should be released this month. you should always use 'attributes' not 'columns' in your queries and let ojb translate the attribut-name into a column-name. hth jakob ps: what do you use ojb for in bedag ? Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
AW: AW: Problem joining two tables with same column name
Thanks a lot Jakob! That was the problem -Ursprüngliche Nachricht- Von: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Gesendet: Montag, 13. März 2006 18:09 An: OJB Users List Betreff: Re: AW: Problem joining two tables with same column name hi josef, you should not prefix the attribute with the tablename. crit.addEqualTo(id) is ok, where id is the name of the attribute not the name of the column. hth jakob Josef Wagner schrieb: Hi Jakob, do you think that I have the same problem? It appears only with m-n. ## ### 1. mapping ### ## !-- m - n -- collection-descriptor name=table_n collection-class=org.apache.ojb.broker.util.collections.ManageableArrayList element-class-ref=de.on_ergy.lakon.data.model.TableN auto-retrieve=true auto-update=object auto-delete=link proxy=true indirection-table=table_m_table_n fk-pointing-to-this-class column=table_m_obj_id/ fk-pointing-to-element-class column=table_n_obj_id/ /collection-descriptor # ### 2. javacode for query ### # crit.addEqualTo(table_n.obj_id, 1); # ### 3. ojb sql output ### # SELECT A0.obj_id, FROM table_m A0 LEFT OUTER JOIN ( table_m_table_n A1 LEFT OUTER JOIN table_n A2 ON A1.table_n_obj_id=A2.obj_id ) ON A0.obj_id=A1.table_m_obj_id WHERE obj_id = 1 ## ### 4. the bug ### ## Now, the problem is the incorrect obj_id without A2 as prefix :o(. So, I get the following logical exception: org.postgresql.util.PSQLException: ERROR: column reference obj_id is ambiguous Thanks a lot! Josef Wagner Original-Nachricht Betreff: Re: AW: Problem joining two tables with same column name Datum:Fri, 03 Mar 2006 19:03:50 +0100 Von: Jakob Braeuchi [EMAIL PROTECTED] Antwort an: OJB Users List ojb-user@db.apache.org An: OJB Users List ojb-user@db.apache.org, [EMAIL PROTECTED] Referenzen: [EMAIL PROTECTED] hi markus, ojb 1.0.5 should be released this month. you should always use 'attributes' not 'columns' in your queries and let ojb translate the attribut-name into a column-name. hth jakob ps: what do you use ojb for in bedag ? Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
AW: Problem joining two tables with same column name
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
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
Re: AW: Problem joining two tables with same column name
hi markus, ojb 1.0.5 should be released this month. you should always use 'attributes' not 'columns' in your queries and let ojb translate the attribut-name into a column-name. hth jakob ps: what do you use ojb for in bedag ? Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: AW: Problem joining two tables with same column name
hi markus, imo the problem is addColumnEqualTo(). in the column-methods the translation flag is false, which means that ojb does not try to translate the attribute-name into a column-name (that's ok for columns). but we also do not prefix the column-name with the alias, and this looks like a bug :( i'll fix it for 1.0.5. jakob Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
AW: Problem joining two tables with same column name
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
Re: AW: Problem joining two tables with same column name
hi markus, do you have this problem on a 1:1 or 1:n relationship ? could you eventually provide a test case ? jakob Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: AW: Problem joining two tables with same column name
hi markus, in your original post i saw that you use addColumnEqualTo // c.setAlias(a1); c.addColumnEqualTo(COLUMN1,034); c.addColumnEqualTo(COLUMN2,78777); // Criteria c2 = new Criteria(); what's the reason no to use addEqualTo() ? jakob Lauber Markus, Bedag schrieb: 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 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01.03.2006 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Problem joining two tables with same column name
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
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
===
