db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <tfisc...@apache.org>
Subject AW: Limiting joins in Oracle
Date Sun, 08 Oct 2006 08:34:43 GMT
Hi Tobias,

Thanks for reporting bach the result of my suggestions. I see that none 
of them has really worked out, sorry for that.

I'd not recommend to change the poulateObject method. This would be coding 
around the error after the harm is done, and changing the internals of 
Torque. This could produce unwanted side-effects.

I have not looked at the code in detail, but from the example you gave 
you are using only the selected rows from one table and not from several 
tables to populate your objects. Is it not possible to use the asColumn 
for the tables which are not used for populating the objects ?

However, in my eyes, the only real solution would be to fix the error in 
the code which creates oracle's join commands. I intend to have a go at 
this in the next few days, so if this works, it'll be in the 3.2.1 
release.

     Thomas

On Thu, 5 Oct 2006, Tobias Hilka wrote:

> 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