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:[EMAIL PROTECTED]
Sent: Tuesday, November 15, 2005 9:38 AM
To: [email protected]
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:[EMAIL PROTECTED]
>Sent: maandag 14 november 2005 18:30
>To: [email protected]
>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>
>
>
>
