You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Tobias Hilka <th...@vps.de> on 2006/10/05 15:35:48 UTC
AW: Limiting joins in Oracle
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
AW: Limiting joins in Oracle
Posted by Thomas Fischer <tf...@apache.org>.
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
>
>