incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francis De Brabandere <franci...@gmail.com>
Subject Re: column ambigously defined / table alias
Date Wed, 18 Aug 2010 07:58:36 GMT
On Wed, Aug 18, 2010 at 7:33 AM, T-Rex <trex521@googlemail.com> wrote:
> Hi Rainer,
>
> thanks for your clue, this is working now. We firstly tried to clone the
> table object, because the alias is set differently then as well. But due to
> the final table columns the cloning did not work properly.
>
> Ok this is a way to do it, but I think this is dirty. Shouldn't the
> framework do this for us in a generic way? So we have to know which tables
> are involved and do this manually.

I agree as this is an issue that keeps popping up in the mailing list.
Maybe there is a cleaner solution than cloning/duplicating table
objects.

>
> Regards,
>
> Pascal
>
> 2010/8/17 Rainer Döbele <doebele@esteam.de>
>>
>> Hi Pascal,
>>
>>
>>
>> if you want to join with the same table twice then you need to create a
>> second instance of that table.
>>
>> Also if you want to do a self join on a table.
>>
>>
>>
>> This is approximately how to do it:
>>
>>
>>
>> public TTableA           T_TABLEA1;
>>
>> public TTableA           T_TABLEA2;
>>
>> public TTableB           T_TABLEB;
>>
>>
>>
>> T_TABLEA1 = new TTableA();
>>
>> T_TABLEA2 = new TTableA();
>>
>> T_TABLEB = new TTableB();
>>
>>
>>
>> cmd.join(T_TABLEA1.C_ID_A, T_TABLEB.C_ID_A, DBJoinType.INNER);
>>
>> cmd.join(T_TABLEA2.C_ID_A, T_TABLEB.C_ID_A, DBJoinType.LEFT);
>>
>>
>>
>> Empire-db generates a different alias for every instance of TTableA and
>> thus give you the result you are expecting.
>>
>> Regards
>> Rainer
>>
>>
>>
>> from: T-Rex [mailto:trex521@googlemail.com]
>>
>> to: empire-db-user@incubator.apache.org
>> re: Re: column ambigously defined / table alias
>>
>>
>>
>> Hello Rainer,
>>
>> thanks for the fast answer.
>> The generated SQL will be something like that:
>>
>> SELECT *
>> FROM
>>  TableA t1 INNER JOIN TableB t2
>>   ON t1.id_a = t2.id_a
>>   LEFT JOIN TableA t1
>>     ON t1.id_a = t2.id_a
>>
>> In our application there are more joins and some where clauses which
>> require the left join here...
>> As you can see the table alias t1 has to be different at the second join
>> to get that statement working.
>>
>> Regards,
>>
>> Pascal
>>
>> 2010/8/17 Rainer Döbele <doebele@esteam.de>
>>
>> Can you give us the sql that has been generated?
>>
>> (you may call cmd.getSelect() to get it)
>>
>>
>>
>> Regards
>>
>> Rainer
>>
>>
>>
>>
>>
>> from: T-Rex [mailto:trex521@googlemail.com]
>>
>> to: empire-db-user@incubator.apache.org
>>
>> re: column ambigously defined / table alias
>>
>>
>>
>> Hello empire-db community,
>>
>> in our application we've got a similar problem like the given example
>> below.
>> We build a command object with different joins depending on different
>> expressions.
>>
>> As you can see we have an inner join and a left outer join on the same
>> tables.
>> If we execute the statement we get the exception: column ambiguously
>> defined.
>> From our point of view the framework is not able to set the table aliases
>> on it's own if required and
>> we do not have the possibility to set the alias manually.
>>
>> Furthermore we see not the possibility to create a full outer join.
>>
>> Example:
>> ###################################################
>>
>> public class PROJECTDB extends DBDatabase
>> {
>>     public TTableA           T_TABLEA;
>>     public TTableB           T_TABLEB;
>>
>>     public static class TTableA extends DBTable
>>     {
>>         // Spaltendefinitionen
>>         public final DBTableColumn  C_ID_A;
>>         public final DBTableColumn  C_VALUE_A;
>>         public final DBTableColumn  C_ID_C;
>>     ...
>>     }
>>     public static class TTableB extends DBTable
>>     {
>>         // Spaltendefinitionen
>>         public final DBTableColumn  C_ID_A;
>>         public final DBTableColumn  C_VALUE_B;
>>     ...
>>     }
>>
>>
>> ...
>>
>> }
>>
>> public class DO_SOME_THING
>> {
>>
>> ...
>>
>>     public method_B (DBCommand cmd)
>>     {
>>         cmd.join  (PROJECTDB.TTableA.C_ID_A, PROJECTDB.TTableB.C_ID_A,
>> DBJoinType.INNER);
>>     }
>>
>>     public method_C (DBCommand cmd)
>>     {
>>         cmd.join  (PROJECTDB.TTableA.C_ID_A, PROJECTDB.TTableB.C_ID_A,
>> DBJoinType.LEFT);
>>     }
>>
>> ..
>>
>>     public method_A
>>     {
>>         DBCommand  cmd = ...;
>>
>>         if (expressionX()) {
>>         method_C(cmd);
>>         }
>>
>>         if (expressionY()) {
>>         method_B(cmd);
>>         }
>> ...
>>         readValuesFromDB(cmd);
>> ...
>>
>>     }
>> ###################################################
>>
>> Do you have any clues?
>>
>> Regards,
>>
>> Pascal
>>
>>
>



-- 
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.

Mime
View raw message