On Wed, Aug 18, 2010 at 7:33 AM, T-Rex <[email protected]> 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 <[email protected]> >> >> 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:[email protected]] >> >> to: [email protected] >> 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 <[email protected]> >> >> Can you give us the sql that has been generated? >> >> (you may call cmd.getSelect() to get it) >> >> >> >> Regards >> >> Rainer >> >> >> >> >> >> from: T-Rex [mailto:[email protected]] >> >> to: [email protected] >> >> 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.
