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]
