db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <tfisc...@apache.org>
Subject Re: Limiting joins in Oracle
Date Fri, 22 Sep 2006 08:07:11 GMT
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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message