Dynamic SQL

[Vio Stan <VS...@cihi.ca>]

Hello
 
I want to create a Dynamic SQL. My mapped statement is something like:
 
<select id="id_1" resultMap="RecordMap" parameterClass="map">
 
<![CDATA[
 
SELECT rn, ProvinceCode
FROM table_1
WHERE rn >= #lower# AND rn <= #upper#
<dynamic prepend="AND">
    <isPropertyAvailable property="provinces">
        ProvinceCode IN (#provinces#)
    </isPropertyAvailable>
</dynamic>
 
ORDER BY rn
]]>

</select>
 
 
What I want is:
 
If "provinces" is not available (is not a key in the input HashMap) the
query should be like:
 
SELECT rn, ProvinceCode FROM table_1 WHERE rn >= #lower# AND rn <=
#upper# ORDER BY rn
 
If "provinces" is available (is a key in the input HashMap and the value
is like: "" 'BC', 'ON', 'SK' ") the query should be like:
 
SELECT rn, ProvinceCode FROM table_1 WHERE rn >= #lower# AND rn <=
#upper# AND ProvinceCode IN ('BC', 'ON', 'SK') ORDER BY rn
 
I tried different combination in the <dynamic> element and I get 
Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended

 
 
Thanks,
Vio
 

Re: Dynamic SQL

[Brandon Goodin <br...@gmail.com>]

You have your dynamic tags wrapped with <![CDATA[...]]>. You can't
wrap tags in CDATA. That tells the xml parser too NOT parse it. Should
be something like the following (Notice the CDATA does not incase any
tags.):

<![CDATA[
SELECT rn, ProvinceCode
FROM table_1
WHERE rn >= #lower# AND rn <= #upper#
]]>
<dynamic prepend="AND">
    <isPropertyAvailable property="provinces">
        ProvinceCode IN (#provinces#)
    </isPropertyAvailable>
</dynamic>

ORDER BY rn

Brandon

On 1/31/06, Vio Stan <VS...@cihi.ca> wrote:
>
> Hello
>
> I want to create a Dynamic SQL. My mapped statement is something like:
>
> <select id="id_1" resultMap="RecordMap" parameterClass="map">
>
> <![CDATA[
>
> SELECT rn, ProvinceCode
> FROM table_1
> WHERE rn >= #lower# AND rn <= #upper#
> <dynamic prepend="AND">
>     <isPropertyAvailable property="provinces">
>         ProvinceCode IN (#provinces#)
>     </isPropertyAvailable>
> </dynamic>
>
> ORDER BY rn
> ]]>
>
> </select>
>
>
> What I want is:
>
> If "provinces" is not available (is not a key in the input HashMap) the
> query should be like:
>
>
> SELECT rn, ProvinceCode FROM table_1 WHERE rn >= #lower# AND rn <= #upper#
> ORDER BY rn
>
> If "provinces" is available (is a key in the input HashMap and the value is
> like: "" 'BC', 'ON', 'SK' ") the query should be like:
>
> SELECT rn, ProvinceCode FROM table_1 WHERE rn >= #lower# AND rn <= #upper#
> AND ProvinceCode IN ('BC', 'ON', 'SK') ORDER BY rn
>
> I tried different combination in the <dynamic> element and I get
> Cause: java.sql.SQLException: ORA-00933: SQL command not properly ended
>
>
>
> Thanks,
> Vio
>