You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by T E Schmitz <ma...@numerixtechnology.de> on 2005/02/24 15:25:53 UTC
[Criteria] where colA=colB
Hello,
I have been using Torque for quite some time now and am probably
overlooking something:
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)
But as far as I can see all Criteria.and(...) methods just take a value
as the comparison argument.
How can I do this?
--
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
Re: [Criteria] where colA=colB
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Andras Balogh wrote:
> I think you can use it like this:
> criteria.add(TransaktionPeer.METHOD, (Object) "T.METHOD =
> TRANSAKTION.METHOD",Criteria.CUSTOM);
Thank you, this works!
Another question: I have built part of my WHERE Criteria elsewhere in
the program and am just passing on the Criteria object. When I "and"
further Criteria to the WHERE clause they get prepended rather than
appended.
I normally use Criterions where the order within the WHERE clause is
crucial. But there doesn't seem to be a way to extract the WHERE
Criterion to add to it. Or is there?
--
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
Re: [Criteria] where colA=colB
Posted by Andras Balogh <an...@reea.net>.
Hello Elisabeth,
T E Schmitz wrote:
>
> 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:
>
> .....
>
> ------------------------
>
> 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?
>
>
If you check here :
http://db.apache.org/torque/criteria-howto.html
"Using the CUSTOM Comparator to check for NULL and NOT NULL" i think
your case is similar.
I think you can use it like this:
criteria.add(TransaktionPeer.METHOD, (Object) "T.METHOD =
TRANSAKTION.METHOD",Criteria.CUSTOM);
Best regards,
Andras.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
torque:jdbc isn't right
Posted by Brandon <ef...@hotmail.com>.
I'm having a problem using the torque:jdbc call. It doesn't seem to be
treating the database as using the native idMethod even though I defined the
DB that way, and used Torque to create and populate the DB. But it doesn't
seem to be pulling the very information that it put into the database.
Surprisingly enough it does create the XML files properly, and everything
seems to be working properly, but it creates a class called BaseIdTablePeer.
The interesting thing is that it creates a duplicate name for a String in
the file. Here's what the generated code looks like:
/** the default database name for this class */
public static final String DATABASE_NAME = "default";
/** the table name for this class */
public static final String TABLE_NAME = "id_table";
/**
* @return the map builder for this peer
* @throws TorqueException Any exceptions caught during processing will be
* rethrown wrapped into a TorqueException
*/
public static MapBuilder getMapBuilder()
throws TorqueException
{
return getMapBuilder(IdTableMapBuilder.CLASS_NAME);
}
/** the column name for the ID_TABLE_ID field */
public static final String ID_TABLE_ID;
/** the column name for the TABLE_NAME field */
public static final String TABLE_NAME;
Notice that the string TABLE_NAME is defined twice. I am not sure why it is
generating things like this. It also indicates that it's using the id_table
stuff rather than the native stuff I defined when I created the DB for the
idMethod. Anyone have any thoughts?
-Brandon
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: [Criteria] where colA=colB
Posted by Howard Lin <xu...@gmail.com>.
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
<ma...@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
Re: [Criteria] where colA=colB
Posted by T E Schmitz <ma...@numerixtechnology.de>.
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
Re: [Criteria] where colA=colB
Posted by Andras Balogh <an...@reea.net>.
Hello,
Can You give a concrete example what are You trying to do?
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:
> Hello,
> I have been using Torque for quite some time now and am probably
> overlooking something:
>
> 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)
>
> But as far as I can see all Criteria.and(...) methods just take a
> value as the comparison argument.
>
> How can I do this?
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org