For complicated query, I would just use BasePeer.executeQuery instead of Criteria.
Howard On Thu, 24 Feb 2005 16:13:13 +0000, T E Schmitz <[EMAIL PROTECTED]> wrote: > Hello Andras, > > Andras Balogh wrote: > > > Can You give a concrete example what are You trying to do? > > In this particular case, I have actually put together a sub-select, > which I retrieve with BasePeer.createQueryString() and add as an > AS-column in the main SELECT: > > select distinct METHOD, > case > when KIND =2 then 2 > when KIND =1 and DISCOUNT is null then 1 > when KIND =1 and DISCOUNT is not null then 99 > end as SUPPLYKIND, > > (select > sum( > case > when TRANSAKTION.KIND =2 then > (-(S.RETAIL_PRICE-coalesce(S.DISCOUNT,0))*S.QUANTITY) > when TRANSAKTION.KIND =1 and SUPPLY.DISCOUNT is null then > (S.RETAIL_PRICE*S.QUANTITY) > when TRANSAKTION.KIND =1 and SUPPLY.DISCOUNT is not null then > (-S.DISCOUNT*S.QUANTITY) > end > ) > from SUPPLY S > inner join TRANSAKTION T on T.TRANSAKTION_PK =S.TRANSAKTION_FK > where > T.THE_TIME >= '1999-01-08' and T.THE_TIME < '2005-02-19' > and T.METHOD = TRANSAKTION.METHOD > and T.KIND=TRANSAKTION.KIND > ) > as amount > > from SUPPLY > inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK > where THE_TIME >= '1999-01-08' and THE_TIME < '2005-02-19' > group by METHOD,KIND,DISCOUNT,QUANTITY > order by METHOD, SUPPLYKIND > > ------------------------ > > I've put this all together with Criteria except: > T.METHOD = TRANSAKTION.METHOD > T.KIND = TRANSAKTION.KIND > which links the inner to the outer SELECT. > > But that complex example apart: how could you compare one column to > another one in the same table? > > I had a look at Criteria.CUSTOM but don't quite understand how to use it. > > > I think this case: > > WHERE TABLE_1.COL_A = TABLE_2.COL_B > > can be handled by: > > crit.addJoin(Table1Peer.ColA,Table2Peer.ColB) > > or this is not the join condition between the tables? > > Also you may try to use Criteria.CUSTOM for the first case or maybe i > > misunderstood You. > > > > Best regards, > > Andras. > > > > T E Schmitz wrote: > > > >> I would like to produce something like: > >> > >> WHERE TABLE_1.COL_A = TABLE_1.COL_B > >> > >> or > >> > >> WHERE TABLE_1.COL_A = TABLE_2.COL_B > >> (in a multi-table join) > >> > > -- > > Regards/Gruß, > > Tarlika Elisabeth Schmitz > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
