Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (herse.apache.org: domain of larry.meadors@gmail.com
 designates 66.249.92.168 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:reply-to:sender:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth;
        b=XN75DLsbHgIT8K24M5Qp4cLHX8T1D6fZLXpBl6Uh0dsH/+7M2t62RVXMJuFXHu4VOR0nq6aboirQorOUWAsH9AfEFzYDmg8zYNfglozNbFq6xiHj2uGmq8Du4ltU3derfjnb9upE21B68c8DPBaF+WAaTk7uncp22fQsnptNdMg=
Message-ID: <a10a823c0707120955x727fe8d2wab16b0466191203c@mail.gmail.com>
Date: Thu, 12 Jul 2007 10:55:13 -0600
From: "Larry Meadors" <lmeadors@apache.org>
Reply-To: lmeadors@apache.org
Sender: larry.meadors@gmail.com
To: user-java@ibatis.apache.org
Subject: Re: ORA-01795: maximum number of expressions in a list
In-Reply-To: <11563250.post@talk.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <11335217.post@talk.nabble.com>
	 <014b01c7b93b$120e13f0$182d4d0a@persistent.co.in>
	 <11563250.post@talk.nabble.com>

Here's my unsolicited opinion on this. :-)

I think you are overusing the iterator, and turning the iBATIS dynamic
SQL tags into a mini programming language is something that seems like
a profoundly bad idea.

XML is not a programming language. What you are trying to accomplish
is most likely trivial in Java. Why make it harder trying to do it in
XML?

Larry


On 7/12/07, indone <bhanujirao@yahoo.com> wrote:
>
> Hi Praveen,
> Thanks for your reply. I think using select AND and OR will degrade the
> performance. So
> I Thought of going for temporary table . Before going to do that  i have
> couple of questions
>
> 1) I have seen Select and Statement tags always execute as prepared
> statement. so is there any way
>     we can make query as statement?
>
> 2)  using iterator is there any way i can get the index from the collection
> ?
>
>     i want to use that index to do some logic..
>
>     <iterate property="geos" open="(" close=")" conjunction=",">
>                         #geos[]#
>      </iterate>
>
> 3) i want to do some calculation using in index like   index/10 can i
> acheive that in ibatis?
>
>
> Thanks
> Venkat
>
>
> Pravin Rane wrote:
> >
> > Hi,
> >
> > There is no direct work around for this problem because you cannot pass
> > more
> > than 1000 values in the IN clause. May be you can try one of the following
> > possible solution.
> > 1. Replace in clause values with the SELECT query if possible. Even if
> > select query returns more than 1000 values it will not create any problem.
> >
> > 2. Create a data structure like list of lists. Each list will have maximum
> > of thousand values. This way you can use nested iterate statement and
> > combine multiple IN statements with OR.
> >
> > I hope this will help
> >
> > Regards
> > ~Pravin
> >
> > -----Original Message-----
> > From: indone [mailto:bhanujirao@yahoo.com]
> > Sent: Thursday, June 28, 2007 6:21 AM
> > To: user-java@ibatis.apache.org
> > Subject: ORA-01795: maximum number of expressions in a list
> >
> >
> > Hi Guys,
> > We are having issue with query exceeding maximum number of expressions >
> > 1000. we are using ibatis. It looks the issue with oracle that wont allow
> > list more than 1000 in in parameter.
> >
> > i am looking for work around that in my sql i need to pass whole set of
> > values at once.so for sure i cant go for multiple calls.
> > second thing is there anyway in ibatis query that i can handle this issue?
> >
> > any suggestions would be appreciated
> > below is the query
> > --------------------------------
> > select to_char(ps_vz_mettrftotal.VZ_ITM_UID) as CATEGORYID,
> >               to_char(sum(PS_VZ_METTRFTOTAL.VZ_IMPRESSION_CNT)) as COUNT
> > from
> >               PS_VZ_METTRFTOTAL, ps_vz_geo_xref where
> >               ps_vz_mettrftotal.VZ_PPC_GEO_ID =
> > ps_vz_geo_xref.VZ_PPC_GEO_ID
> >               and ps_vz_mettrftotal.VZ_PPC_GEO_ID in
> >               <iterate property="geos" open="(" close=")" conjunction=",">
> >                       #geos[]#
> >               </iterate>
> >               group by ps_vz_mettrftotal.VZ_ITM_UID
> >
> >
> > --
> > View this message in context:
> > http://www.nabble.com/ORA-01795%3A-maximum-number-of-expressions-in-a-list-t
> > f3991841.html#a11335217
> > Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >
> >
> >
>
> --
> View this message in context: http://www.nabble.com/ORA-01795%3A-maximum-number-of-expressions-in-a-list-tf3991841.html#a11563250
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>