Return-Path: 
 <user-java-return-5007-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 24628 invoked from network); 15 Nov 2005 15:13:09 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 15 Nov 2005 15:13:09 -0000
Received: (qmail 8410 invoked by uid 500); 15 Nov 2005 15:13:06 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 8394 invoked by uid 500); 15 Nov 2005 15:13:05 -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 8383 invoked by uid 99); 15 Nov 2005 15:13:05 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 15 Nov 2005 07:13:05 -0800
X-ASF-Spam-Status: No, hits=1.6 required=10.0
	tests=DEAR_SOMETHING,UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: neutral (asf.osuosl.org: local policy)
Received: from [204.136.66.110] (HELO dfwmail003.pbsg.com) (204.136.66.110)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 15 Nov 2005 07:12:56 -0800
Received: from ([156.81.229.59])
	by dfwmail003.pbsg.com with ESMTP  id KP-VYNT4.63300046;
	Tue, 15 Nov 2005 09:12:13 -0600
Received: from pbswmz00067.corp.pep.pvt ([156.81.229.6]) by
 planxr14.fritolay.pvt with SMTP (Microsoft Exchange Internet Mail Service
 Version 5.5.2657.72)
	id W21LWVDX; Tue, 15 Nov 2005 09:12:12 -0600
Received: from planxr0b.fritolay.pvt ([156.81.229.192]) by
 pbswmz00067.corp.pep.pvt with InterScan Messaging Security Suite;
 Tue, 15 Nov 2005 09:12:13 -0600
Received: by PLANXR0B.fritolay.pvt with Internet Mail Service (5.5.2657.72)
	id <W7RLTTBH>; Tue, 15 Nov 2005 09:12:13 -0600
Message-ID: 
 <F9994DF29A98C848AFA24E939471E5B60157B5DF@pepwmu00195.corp.pep.pvt>
From: "Voorhoeve, Niels {PBG}" <niels.voorhoeve@pepsi.com>
To: user-java@ibatis.apache.org
Subject: RE: Dynamic statement
Date: Tue, 15 Nov 2005 09:12:05 -0600
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2657.72)
Content-Type: text/plain
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

Try this:
Get rid of the last static 'AND'.  Then put prepend="AND" in your dynamic
tag. Move the parentheses inside the dynamic tag.  Hope it works.


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


-----Original Message-----
From: John Chien [mailto:john.chien@ncmail.net] 
Sent: Tuesday, November 15, 2005 9:38 AM
To: user-java@ibatis.apache.org
Subject: Re: Dynamic statement

Niels:

Thank you for the idea.
However, it seems not work.
Everytime, I tried to run. It just throws exception.

John Chien
===================================================================
I use it as:

<dynamic  removeFirstPrepend="true">

  <isNotEmpty prepend="OR" property="fields[0].value" > 
               p.PMT_FISCAL_YR       = #fields [0].value#

  </isNotEmpty> 
  <isNotEmpty prepend="or" property="fields[1].value" > 
               p.PMT_DEPOSIT_DT      = TO_DATE(#fields[1].value#,
'MMDDYYYY')
  </isNotEmpty> 
  <isNotEmpty prepend="or" property="fields[2].value" > 
                c.CORP_ID              = #fields[2].value# 
  </isNotEmpty>

</dynamic>

======================================================================
Niels Beekman wrote:

>You should use removeFirstPrepend="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] 
>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 = c.CORP_ID AND c.CORP_ID = a.CORP_ID AND (OR <st1> OR
<st2> OR <st3>)
>which certainly does not make sense. 
>
>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: 
>First off by "not run well"  do you mean that there is an error?  if  so
please post it. 
>
>Second turn on logging to make sure the sql appears as you would  expect it
to. 
>
>Third it appears that you are sending in an array of objects as
parameters.  I would suggest that you use a java bean with the 3  properties
( year, deposit, corp_id), or a map of these.  This will  enable you not to
hard code the array indices. 
>
>nathan 
>
>
>On Nov 14, 2005, at 9:53 AM, John Chien wrote: 
>
>
>Dear Sir: 
>
>I have a question related to dynamic statement 
>As shown below, I have a select statement for data in  three tables 
>The GUI has three input fields. Their value can not be determined  before
hand. 
>
>Besides of doing the join of the tables, I want to have a dynamic  where
clause so that the where 
>clause syntax is determined by the value in the fields. 
>
>The statement below does not run well. 
>How can I fix it ? 
>
>Thanks, 
>
>John Chien 
>
>**********************************************************************
******************* 
>
>
>SELECT  ----------------------------------------------  (Omit) 
>        FROM  EH.BETS_PAYMENT p, EH.BETS_CORPORATION c,  EH.BETS_ADDRESS a 
>WHERE p.CORP_ID = c.CORP_ID AND c.CORP_ID = a.CORP_ID AND 
>( 
>      <dynamic> 
>             <isNotEmpty prepend="OR" property="fields[0].value" > 
>               p.PMT_FISCAL_YR       = #fields [0].value#
</isNotEmpty> 
>           <isNotEmpty prepend="or" property="fields[1].value" > 
>               p.PMT_DEPOSIT_DT      = TO_DATE(#fields[1].value#,
'MMDDYYYY')                                           </isNotEmpty> 
>             <isNotEmpty prepend="or" property="fields[2].value" > 
>                c.CORP_ID              = #fields[2].value# 
>             </isNotEmpty> 
>      </dynamic> 
>) 
>**********************************************************************
************************* 
><john.chien.vcf> 
>
>  
>