I am rather sure this is a known problem, see
http://issues.apache.org/jira/browse/TORQUE-10
Are you sure you copied the query correctly ? The problem used to be the
"select A.*" or "select B.*" (Cannot remember which one, maybe both are
needed) in the query which used to run on two fieldnames like
mytablealias1.FIELDNAME and mytablealias2.FIELDNAME. This resulted in to
equal fieldnames, as the error says.
Thomas
On Thu, 21 Sep 2006, Tobias Hilka wrote:
Hi everybody,
We are facing the following problem:
We would like to execute a limited query over a join of two (or more)
database tables in oracle.
We build our Criteria like this (more or less):
crit.setOffset(0);
crit.setLimit(50);
crit.addJoin(mytablealias1.FIELDNAME, mytablealias2.FIELDNAME,
Critiera.INNER_JOIN);
crit.addSelectColumn(mytablealias1.FIELDNAME);
crit.addAlias(mytablealias1, TABLE1);
crit.addAlias(mytablealias2, TABLE2);
The SQL-query string that is generated looks like this:
SELECT B.* FROM
( SELECT A.*, rownum AS TORQUE$ROWNUM FROM
( SELECT MYTABLEALIAS1.FIELDNAME
FROM T_TABLE1 mytablealias1 INNER JOIN TABLE mytablealias2 ON
mytablealias1.FIELDNAME=mytablealias1.FIELDNAME
) A
) B WHERE B.TORQUE$ROWNUM <= 50
When executing this query, torque (and the tool we are using TOAD)) tells us
the following error code:
ORA-00918 column ambiguously defined.
It seems that Oracle takes all fields from the innermost select and cuts off
the alias names, resulting in two columns with the name "FIELDNAME". I
understand the problem oracle is having at this point, but is there any
solution to this problem?
Thanks in advance.
Best regards,
Tobias Hilka
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
