ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vio Stan" <VS...@cihi.ca>
Subject Dynamic SQL
Date Tue, 31 Jan 2006 14:22:53 GMT
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
 

Mime
View raw message