| John,
The WIKI is always a great place to start :)
here is the specific FAQ about logging.
Try to get that to work first.
I would also change your dynamic to this.
<dynamic 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>
This way if none of the parameters are sent in your sql will still be valid. In your version if no parameters were passed in you would end up with AND () which is NOT valid. The prepend in the dynamic block will override the first true condition inside of it. You can find more on the in the developers manual in the Dynamic Mapped Statements section, pg 35 in my version.
Hope this helps
On Nov 14, 2005, at 10:30 AM, John Chien wrote:
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>
<john.chien.vcf> |
