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 subselect,
which I retrieve with BasePeer.createQueryString() and add as an
AScolumn 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_PRICEcoalesce(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 >= '19990108' and T.THE_TIME < '20050219'
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 >= '19990108' and THE_TIME < '20050219'
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 multitable join)
>>

Regards/Gruß,
Tarlika Elisabeth Schmitz

To unsubscribe, email: torqueuserunsubscribe@db.apache.org
For additional commands, email: torqueuserhelp@db.apache.org
