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