I have a query that i need to cut back by multiple and optional sets of
inlist keys where if there are more than one series of inlists, I need to OR
them together and AND them with the rest of the where clause.
This is what I did for a query that had two lists of Integers that each were
optional. This is apart of a <where> clause statement with other where
clause filters.
<choose>
<when test="filter != null and filter.categoryCodes != null
and filter.subCategoryCodes != null">
and (
MD_CRDT_INTN_CATG_ID IN
<foreach item="item" index="index"
collection="filter.categoryCodes"
open="(" separator="," close=")">#{item}
</foreach>
or
MD_CRDT_INTN_SUB_CATG_ID IN
<foreach item="item" index="index"
collection="filter.subCategoryCodes"
open="(" separator="," close=")">#{item}
</foreach>
)
</when>
<when test="filter != null and filter.categoryCodes != null
and filter.subCategoryCodes == null">
and MD_CRDT_INTN_CATG_ID IN
<foreach item="item" index="index"
collection="filter.categoryCodes"
open="(" separator="," close=")">#{item}
</foreach>
</when>
<when test="filter != null and filter.categoryCodes == null
and filter.subCategoryCodes != null">
and MD_CRDT_INTN_SUB_CATG_ID IN
<foreach item="item" index="index"
collection="filter.subCategoryCodes"
open="(" separator="," close=")">#{item}
</foreach>
</when>
</choose>
This works, but now I have another scenario where the query will have to
support up to 5 different lists of Integers that need to be OR'd together
and are all optional.
The above approach works ok for two lists, but it gets messy for 5.
Thanks!
Dustin
--
View this message in context:
http://old.nabble.com/Suggestions-for-handling-multiple-optional-inlists-tp28273925p28273925.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
