Return-Path: 
 <user-java-return-4988-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 58273 invoked from network); 14 Nov 2005 19:08:02 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 14 Nov 2005 19:08:02 -0000
Received: (qmail 28047 invoked by uid 500); 14 Nov 2005 19:07:57 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 28001 invoked by uid 500); 14 Nov 2005 19:07:56 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 27959 invoked by uid 99); 14 Nov 2005 19:07:56 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 Nov 2005 11:07:56 -0800
X-ASF-Spam-Status: No, hits=1.6 required=10.0
	tests=DEAR_SOMETHING
X-Spam-Check-By: apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Received: from [83.247.21.91] (HELO wis.nl) (83.247.21.91)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 Nov 2005 11:07:47 -0800
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Dynamic statement
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Date: Mon, 14 Nov 2005 20:06:50 +0100
Message-ID: <50CA25BD6EEA954FA592C097399942E30931365A@CM1.wis.local>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Dynamic statement
thread-index: AcXpQQgeke0yrcy9RF+toG5vi81YWAADV3Kw
From: "Niels Beekman" <n.beekman@wis.nl>
To: <user-java@ibatis.apache.org>
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

You should use removeFirstPrepend=3D"true" on the dynamic-tag, this =
causes iBATIS to skip the first prepend encountered.

Hope this helps,

Niels

________________________________________
From: John Chien [mailto:john.chien@ncmail.net]=20
Sent: maandag 14 november 2005 18:30
To: user-java@ibatis.apache.org
Subject: Re: Dynamic statement


Nathan:

How can I turn on the logging of ibatis ?
The statement just does not run.

I think the problem is that the three statement in the <dynamic> =
</dynamic>
will produce something like (OR <st1> OR <st2> OR <st3>)

Thus the where clause will become like:

WHERE p.CORP_ID =3D c.CORP_ID AND c.CORP_ID =3D a.CORP_ID AND (OR <st1> =
OR <st2> OR <st3>)
which certainly does not make sense.=20

However, I do not know I can get rid of the first OR because any of =
three fields might have value or no value.

Thanks,

John Chien

Nathan Maves wrote:=20
First off by "not run well"=A0 do you mean that there is an error?=A0 =
if=A0 so please post it.=20

Second turn on logging to make sure the sql appears as you would=A0 =
expect it to.=20

Third it appears that you are sending in an array of objects as=A0 =
parameters.=A0 I would suggest that you use a java bean with the 3=A0 =
properties ( year, deposit, corp_id), or a map of these.=A0 This will=A0 =
enable you not to hard code the array indices.=20

nathan=20


On Nov 14, 2005, at 9:53 AM, John Chien wrote:=20


Dear Sir:=20

I have a question related to dynamic statement=20
As shown below, I have a select statement for data in=A0 three tables=20
The GUI has three input fields. Their value can not be determined=A0 =
before hand.=20

Besides of doing the join of the tables, I want to have a dynamic=A0 =
where clause so that the where=20
clause syntax is determined by the value in the fields.=20

The statement below does not run well.=20
How can I fix it ?=20

Thanks,=20

John Chien=20

********************************************************************** =
*******************=20


SELECT=A0 ----------------------------------------------=A0 (Omit)=20
=A0=A0=A0=A0=A0=A0=A0 FROM=A0 EH.BETS_PAYMENT p, EH.BETS_CORPORATION =
c,=A0 EH.BETS_ADDRESS a=20
WHERE p.CORP_ID =3D c.CORP_ID AND c.CORP_ID =3D a.CORP_ID AND=20
(=20
=A0=A0=A0=A0=A0 <dynamic>=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <isNotEmpty prepend=3D"OR" =
property=3D"fields[0].value" >=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
p.PMT_FISCAL_YR=A0=A0=A0=A0=A0=A0 =3D #fields =
[0].value#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
</isNotEmpty>=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <isNotEmpty prepend=3D"or" =
property=3D"fields[1].value" >=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
p.PMT_DEPOSIT_DT=A0=A0=A0=A0=A0 =3D TO_DATE(#fields[1].value#,=A0 =
'MMDDYYYY')=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
</isNotEmpty>=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <isNotEmpty prepend=3D"or" =
property=3D"fields[2].value" >=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
c.CORP_ID=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =3D #fields[2].value#=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 </isNotEmpty>=20
=A0=A0=A0=A0=A0 </dynamic>=20
)=20
********************************************************************** =
*************************=20
<john.chien.vcf>=20