ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From indone <bhanuji...@yahoo.com>
Subject Re: ORA-01795: maximum number of expressions in a list
Date Thu, 12 Jul 2007 18:43:59 GMT

Yes you are correct. I just want to know the options..as i have asked to try
this option by dbas.
Let me know your opinion how to achieve this query..

i want to convert the query something like this..

select to_char(ps_vz_mettrftotal.VZ_ITM_UID) as CATEGORYID,
to_char(sum(PS_VZ_METTRFTOTAL.VZ_IMPRESSION_CNT)) as COUNT
 from 
 
 ( select ps_vz_mettrftotal.VZ_ITM_UID , PS_VZ_METTRFTOTAL.VZ_IMPRESSION_CNT
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 ( 1.. 1000)
   union 
select ps_vz_mettrftotal.VZ_ITM_UID , PS_VZ_METTRFTOTAL.VZ_IMPRESSION_CNT
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 ( 1001.. 2000)

  union
  select ps_vz_mettrftotal.VZ_ITM_UID , PS_VZ_METTRFTOTAL.VZ_IMPRESSION_CNT
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 ( 2001.. 3000)

  )
      group by ps_vz_mettrftotal.VZ_ITM_UID


The earlier query looks like this

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



let me know your suggestions

Thanks
Venkat



Larry Meadors-2 wrote:
> 
> 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.
>>
>>
> 
> 

-- 
View this message in context: http://www.nabble.com/ORA-01795%3A-maximum-number-of-expressions-in-a-list-tf3991841.html#a11566353
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message