Return-Path: 
 <user-java-return-19156-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 81105 invoked from network); 17 Apr 2010 04:09:36 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
  by 140.211.11.9 with SMTP; 17 Apr 2010 04:09:36 -0000
Received: (qmail 93085 invoked by uid 500); 17 Apr 2010 03:41:34 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 93066 invoked by uid 500); 17 Apr 2010 03:41:31 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 93057 invoked by uid 99); 17 Apr 2010 03:41:30 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 17 Apr 2010 03:41:30 +0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=FREEMAIL_FROM,SPF_HELO_PASS,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Received: from [216.139.236.158] (HELO kuber.nabble.com) (216.139.236.158)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 17 Apr 2010 03:41:25 +0000
Received: from isper.nabble.com ([192.168.236.156])
	by kuber.nabble.com with esmtp (Exim 4.63)
	(envelope-from <lists@nabble.com>)
	id 1O2yu0-0006hi-Jm
	for user-java@ibatis.apache.org; Fri, 16 Apr 2010 20:41:04 -0700
Message-ID: <28273925.post@talk.nabble.com>
Date: Fri, 16 Apr 2010 20:41:04 -0700 (PDT)
From: duslow <duslow@gmail.com>
To: user-java@ibatis.apache.org
Subject: Suggestions for handling multiple optional inlists
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Nabble-From: duslow@gmail.com


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: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org