ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Niels Beekman" <n.beek...@wis.nl>
Subject RE: Dynamic SQL
Date Tue, 31 Jan 2006 14:26:46 GMT
You must either use the iterate-tag (recommended, see previous posts about this) or use $-syntax:
IN ($provinces$).

Related Wiki for $-syntax (linewrap):
http://opensource2.atlassian.com/confluence/oss/display/IBATIS/How+do+I+pass+in+a+dynamic+table+name+-+or+other+uncommon+parameters

Niels

________________________________________
From: Vio Stan [mailto:VStan@cihi.ca] 
Sent: dinsdag 31 januari 2006 15:23
To: user-java@ibatis.apache.org
Subject: Dynamic SQL

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