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