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
smime.p7s
Description: S/MIME cryptographic signature
