db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tobias Hilka" <thi...@vps.de>
Subject AW: Limiting joins in Oracle
Date Thu, 05 Oct 2006 13:35:48 GMT
Hello Thomas,

Thank you for your help. But there are still some problems left:

I tried out your second suggestion (the one with the asColumn). 
Since our tables are quite large (more than 100 columns), joining two or
more table could easily end up in having more than 200 select columns.
Escaping each of this columns with an asColumn entry may cause oracle to
fail because the statement produced by torque is too long.

Now my idea was to check for equaly named columns in the joined tables and
use the Criteria.AsColumn solution only for them. This would help us with
the length of the statement since only the necessary columns are handled
seperately. 
But if we do this, the order of the select columns in the results returned
from the database is not the original one (since torque first processes the
select columns and afterwards the asColumns [see
SQLBuilder.buildQueryClause()]). Therefore we can not use populateObject to
fill the java object with the values from the database.
Actually I hoped to replace the original select column when adding the same
column to the Criteria.AsColumn list.

Can you give me some advice on how to continue? I think writing my own
populateObject method is not a good idea.

Concerning your other suggestions:
- renaming the column names is not possible (as you already supposed).
- Using the Criteria.CUSTOM: This would end up in writing pure sql code
which can be quite different for MSSQL and Oracle. Not a practical solution.


As you told me, the problem with the oracle database is already known. Will
this problem be solved in your new release 3.2.1?

Best regards,

Tobias





-----Urspr√ľngliche Nachricht-----
Von: Thomas Fischer [mailto:tfischer@apache.org] 
Gesendet: Freitag, 22. September 2006 10:35
An: Apache Torque Developers List
Betreff: Re: Limiting joins in Oracle

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