...And actually, I'm not even certain the removeFirstPrepend="true"
attribute is necessary in the isNotEmpty tag for the area property.
You may want to test it with and without that attribute. Also, be sure
you can see what the sql statement that is being generated by setting
up logging. This always helps when you are assembling dynamic sql.
Brandon
On 1/27/06, Brandon Goodin <[EMAIL PROTECTED]> wrote:
> Building on Yusuf's suggestions... Something like the following should work:
>
> <select id="selectALL" parameterClass="PersoninfoPO"
> resultClass="PersoninfoPO">
> select * from cto.personinfovo
> <dynamic prepend="where">
> <isNotEmpty property="id">
> id=#id#
> </isNotEmpty>
> <isNotEmpty property="area" compareValue="ALL"
> removeFirstPrepend="true">
> <isEqual prepend="and" property="area" compareValue="ALL">
> area in ('SC','EC','CC','NC','NE','SW','NW')
> </isEqual>
> <isNotEqual prepend="and" property="area" compareValue="ALL">
> area=#area#
> </isEqual>
> </isNotEmpty>
> </dynamic>
> </select>
>
> Hope that works,
> Brandon
>
> On 1/27/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Dear Yusuf,
> >
> > Thanks for your warm-hearted reply.
> >
> > Sorry I miss the case 1, it should be:
> > "select * from cto.personinfovo where id=#id# and area=#area# "
> >
> > So I think your statement isn't working too.
> >
> > Have a nice weekend.
> >
> > Thanks and Best Regards
> > Steven
> >
> >
> >
> >
> >
> > On 1/27/06, Yusuf <[EMAIL PROTECTED]> wrote:
> > > Hello, i think the only thing's missing from your query (in the first
> > > message in this thread) is the property removeFirstPrepend="true" in
> > > the tags inside the <dynamic> tag, for example like this:
> > >
> > > <select id="selectALL" parameterClass="PersoninfoPO"
> > > resultClass="PersoninfoPO">
> > > select * from cto.personinfovo
> > > <dynamic prepend="where">
> > > <isNotEmpty prepend="and" property="id"
> > > removeFirstPrepend="true">
> > > id=#id#
> > > </isNotEmpty>
> > > <isEqual prepend="and" property="area" compareValue="ALL">
> > > area in
> > ('SC','EC','CC','NC','NE','SW','NW')
> > > </isEqual>
> > > </dynamic>
> > > </select>
> > >
> > > And also, for a lot more easier way to debug your sql statements, try
> > > using log4j, you can read the guide in iBatis Data Mapper 2.0 Developer
> > > Guide (page 47 in the pdf) about Logging SqlMapActivity.
> > >
> > > Yusuf.
> > >
> > > -----Original Message-----
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, January 27, 2006 1:31 PM
> > > To: [email protected]
> > > Subject: Re: Dynamic sql statement
> > >
> > >
> > > Dear Brandon,
> > >
> > > Thanks for you reply.
> > >
> > > I think the problem is not the "property" because I try many times.
> > > Following is what I want to accopmplish:
> > > select * from cto.personinfovo where id=#id# and area=#area#
> > >
> > > There are 2 case happen when user do action:
> > > Case 1. when user doesn't input anything in "area" field, it will be:
> > > "select * from cto.personinfovo where id=#id# "
> > >
> > > Case 2. when user input "ALL" in "area" field, it will be:
> > > "select * from cto.personinfovo where id=#id# and area in
> > > ('SW','SC','NC')"
> > >
> > > At the same time, the "id" is dynamic too, this is the most difficult I
> > > can't solve.
> > >
> > > Is it clear for it?
> > >
> > > Thanks and Best Regards
> > > Steven
> > >
> > >
> > > On 1/27/06, Brandon Goodin < [EMAIL PROTECTED]
> > > <mailto:[EMAIL PROTECTED]> > wrote:
> > >
> > > <isNotEmpty prepend="and" propery="area>
> > >
> > > You mispelled *property*
> > >
> > > Other than that, what is NOT working? are you getting an error? Is the
> > > result wrong? What is it you want to accomplish?
> > >
> > > Brandon
> > >
> > > On 1/26/06, [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> <
> > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] > > wrote:
> > > > Dear all,
> > > >
> > > > I want to run one dynamic query like this:
> > > >
> > > > select * from cto.personinfovo where id=#id# and area=#area#
> > > >
> > > > But for "area" field, is has two conditions, one is for "ALL" and one
> > > is the
> > > > input value, the ibatis XML file is like this and it's not working
> > > well,
> > > >
> > > > <select id="selectALL" parameterClass="PersoninfoPO"
> > > > resultClass="PersoninfoPO">
> > > > <![CDATA[ select * from cto.personinfovo ]]>
> > > > <dynamic prepend="where">
> > > > <isNotEmpty prepend="and" property="id">
> > > > <![CDATA[ (id=#id#) ]]>
> > > > </isNotEmpty>
> > > > <isNotEmpty prepend="and" propery="area>
> > > > ( <![CDATA[ (area=#area#) ]]>
> > > > <isEqual prepend="or" property="area" compareValue="ALL">
> > > > <![CDATA[(area in
> > > > ('SC','EC','CC','NC','NE','SW','NW'))]]>
> > > > </isEqual> )
> > > > </isNotEmpty>
> > > > </dynamic>
> > > > </select>
> > > >
> > > > Could any one give me the help?
> > > >
> > > > Thanks so much!
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>
