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:34:59 GMT
Sorry, I forgot to say what you might do to resolve your problem

- Use Criteria.CUSTOM to create your Query manually (not nice, but this 
will work for sure)

- I'm not sure whether aliasing one conflicting Column name using 
Criteria.AsColumn solves your problem, but it might be worth trying. This 
would be the cleanest solution.

- Just for the sake of completeness: you can rename one of the conflicting 
column names. I'm not really proposing this.

     Thomas

On Fri, 22 Sep 2006, Thomas Fischer wrote:

> 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
>
>

---------------------------------------------------------------------
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