ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pravin Rane" <pravinran...@gmail.com>
Subject RE: ORA-01795: maximum number of expressions in a list
Date Thu, 28 Jun 2007 04:16:11 GMT
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.


Mime
View raw message