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!
> > >
> > >
> > >
> >
> >
> >
>
>
