db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tobias Hilka" <thi...@vps.de>
Subject Limiting joins in Oracle
Date Thu, 21 Sep 2006 16:48:58 GMT
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

Mime
View raw message