From torque-user-return-5850-apmail-db-torque-user-archive=db.apache.org@db.apache.org Thu Feb 24 16:39:21 2005 Return-Path: Delivered-To: apmail-db-torque-user-archive@www.apache.org Received: (qmail 54228 invoked from network); 24 Feb 2005 16:39:21 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur-2.apache.org with SMTP; 24 Feb 2005 16:39:21 -0000 Received: (qmail 64308 invoked by uid 500); 24 Feb 2005 16:39:20 -0000 Delivered-To: apmail-db-torque-user-archive@db.apache.org Received: (qmail 64296 invoked by uid 500); 24 Feb 2005 16:39:19 -0000 Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Subscribe: List-Help: List-Post: List-Id: "Apache Torque Users List" Reply-To: "Apache Torque Users List" Delivered-To: mailing list torque-user@db.apache.org Received: (qmail 64281 invoked by uid 99); 24 Feb 2005 16:39:19 -0000 X-ASF-Spam-Status: No, hits=0.2 required=10.0 tests=RCVD_BY_IP,SPF_HELO_PASS,SPF_PASS,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: domain of xuhua.lin@gmail.com designates 64.233.170.196 as permitted sender) Received: from rproxy.gmail.com (HELO rproxy.gmail.com) (64.233.170.196) by apache.org (qpsmtpd/0.28) with ESMTP; Thu, 24 Feb 2005 08:39:19 -0800 Received: by rproxy.gmail.com with SMTP id a41so336484rng for ; Thu, 24 Feb 2005 08:39:17 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=WtjNSKx89zHP6fyQ7tYhxJnMfCwl2D1B9gcj+5PKzUv983pNWkmxqfagzd+EjJK/3bevYr0JaXBW8x/p3sD91UWyoLQVChHH6GqRTF4lMBZO6E3svHfveWZBXSIo9/YoGWtdkcm6mbrS040sAbf3ZEMTU8xTaIFzilQNr2xJ0hA= Received: by 10.39.1.35 with SMTP id d35mr167543rni; Thu, 24 Feb 2005 08:39:17 -0800 (PST) Received: by 10.38.86.70 with HTTP; Thu, 24 Feb 2005 08:39:17 -0800 (PST) Message-ID: <37d34e6b050224083934877876@mail.gmail.com> Date: Thu, 24 Feb 2005 11:39:17 -0500 From: Howard Lin Reply-To: Howard Lin To: Apache Torque Users List , mailreg@numerixtechnology.de Subject: Re: [Criteria] where colA=colB In-Reply-To: <421DFD19.3020209@numerixtechnology.de> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable References: <421DE3F1.40100@numerixtechnology.de> <421DECA6.5000106@reea.net> <421DFD19.3020209@numerixtechnology.de> X-Virus-Checked: Checked X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N 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 wrote: > Hello Andras, >=20 > Andras Balogh wrote: >=20 > > Can You give a concrete example what are You trying to do? >=20 > 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: >=20 > select distinct METHOD, > case > when KIND =3D2 then 2 > when KIND =3D1 and DISCOUNT is null then 1 > when KIND =3D1 and DISCOUNT is not null then 99 > end as SUPPLYKIND, >=20 > (select > sum( > case > when TRANSAKTION.KIND =3D2 then > (-(S.RETAIL_PRICE-coalesce(S.DISCOUNT,0))*S.QUANTITY) > when TRANSAKTION.KIND =3D1 and SUPPLY.DISCOUNT is null then > (S.RETAIL_PRICE*S.QUANTITY) > when TRANSAKTION.KIND =3D1 and SUPPLY.DISCOUNT is not null then > (-S.DISCOUNT*S.QUANTITY) > end > ) > from SUPPLY S > inner join TRANSAKTION T on T.TRANSAKTION_PK =3DS.TRANSAKTION_FK > where > T.THE_TIME >=3D '1999-01-08' and T.THE_TIME < '2005-02-19' > and T.METHOD =3D TRANSAKTION.METHOD > and T.KIND=3DTRANSAKTION.KIND > ) > as amount >=20 > from SUPPLY > inner join TRANSAKTION on TRANSAKTION_PK =3DTRANSAKTION_FK > where THE_TIME >=3D '1999-01-08' and THE_TIME < '2005-02-19' > group by METHOD,KIND,DISCOUNT,QUANTITY > order by METHOD, SUPPLYKIND >=20 > ------------------------ >=20 > I've put this all together with Criteria except: > T.METHOD =3D TRANSAKTION.METHOD > T.KIND =3D TRANSAKTION.KIND > which links the inner to the outer SELECT. >=20 > But that complex example apart: how could you compare one column to > another one in the same table? >=20 > I had a look at Criteria.CUSTOM but don't quite understand how to use it. >=20 > > I think this case: > > WHERE TABLE_1.COL_A =3D 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 =3D TABLE_1.COL_B > >> > >> or > >> > >> WHERE TABLE_1.COL_A =3D TABLE_2.COL_B > >> (in a multi-table join) > >> >=20 > -- >=20 > Regards/Gru=DF, >=20 > Tarlika Elisabeth Schmitz >=20 > --------------------------------------------------------------------- > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org > For additional commands, e-mail: torque-user-help@db.apache.org >=20 > --------------------------------------------------------------------- To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org For additional commands, e-mail: torque-user-help@db.apache.org