Re: Dynamic sql statement

[Jeff Butler <je...@gmail.com>]

Try this...

<select id="getObituaParams" resultClass="obituary"
               parameterClass="basicObituary" cacheModel="obituaryCache">
               select
               THUMB_PHOTO_LINK as thumbPhotoLink, LEADIN as leadin FROM
               OBITUARY where status=1
               <dynamic prepend="and (">
                       <isNotEmpty prepend="or"  property="firstName">
                               upper(FIRST_NAME)=upper(#firstName#)
                       </isNotEmpty>
                       <isNotEmpty prepend="or" property="surName">
                               upper(SUR_NAME)=upper(#surName#)
                       </isNotEmpty>
                  )
               </dynamic>
</select>

Jeff Butler


On Nov 7, 2007 3:28 PM, Odelya Glick <od...@jpost.com> wrote:

> Hi,
>
> I have the following SQL statement:
> <select id="getObituaParams" resultClass="obituary"
>                parameterClass="basicObituary" cacheModel="obituaryCache">
>                select
>                THUMB_PHOTO_LINK as thumbPhotoLink, LEADIN as leadin FROM
>                OBITUARY
>                <dynamic prepend="where">
>                        <isNotEmpty prepend="OR"  property="firstName">
>                                upper(FIRST_NAME)=upper(#firstName#) and
> status=1
>                        </isNotEmpty>
>                        <isNotEmpty prepend="OR" property="surName">
>                                upper(SUR_NAME)=upper(#surName#) and
> status=1
>                        </isNotEmpty>
>                </dynamic>
>        </select>
> I would like the dynamic sentence to be executed along with the condition:
> If status=1 so it I will be:
> Select * from OBITUARY where status=1 and (the dynamic sentence here).
>
> How do I do it? Please note, that the dynamic sentence may not be build
> since both firstname and suname will be empty, and only the status=1 will
> be
> executed..
>
> Thanks
>
>
> Odelya Glick
> JPost.com web-programmer
> 050-2052075
>
>
>