db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From T E Schmitz <mail...@numerixtechnology.de>
Subject Re: [Criteria] where colA=colB
Date Thu, 24 Feb 2005 16:13:13 GMT
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: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message