db-torque-user mailing list archives

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

---------------------------------------------------------------------
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