incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From T-Rex <trex...@googlemail.com>
Subject Re: column ambigously defined / table alias
Date Wed, 18 Aug 2010 05:33:47 GMT
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.

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

Mime
View raw message