You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by indone <bh...@yahoo.com> on 2007/07/12 18:05:43 UTC
RE: ORA-01795: maximum number of expressions in a list
Hi Praveen,
Thanks for your reply. I think using select AND and OR will degrade the
performance
I Thought of going to temporary table . Before going to thought 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 index from the collection ?
i want to use that index to do some logic..
<iterate property="geos" open="(" close=")" conjunction=",">
#geos[]#
</iterate>
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.
RE: ORA-01795: maximum number of expressions in a list
Posted by Poitras Christian <Ch...@ircm.qc.ca>.
For point 2 - I don't think it's possible right now, but creating a
patch for this should be easy.
Christian
-----Original Message-----
From: indone [mailto:bhanujirao@yahoo.com]
Sent: Thursday, 12 July 2007 12:06
To: user-java@ibatis.apache.org
Subject: RE: ORA-01795: maximum number of expressions in a list
Hi Praveen,
Thanks for your reply. I think using select AND and OR will degrade the
performance I Thought of going to temporary table . Before going to
thought 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 index from the collection ?
i want to use that index to do some logic..
<iterate property="geos" open="(" close=")" conjunction=",">
#geos[]#
</iterate>
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-li
st-tf3991841.html#a11563250
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: cursor or multiple parameters
Posted by Larry Meadors <lm...@apache.org>.
With that database design, there is really no other way.
You could use a Map, I suppose.
Larry
On 7/13/07, Severin Ecker <se...@gmx.at> wrote:
> hi Larry,
>
> yes or course, but there i need either a new type that i hand over to
> the sqlmap unless i can use more than one parameters.. or a list.
>
> your solution (which is the one i'm using atm) requires another type
> definiton since i can't use the domain object
>
> public class Bla { String hello; String world; //rest omitted }
>
> where the 'new value' field is not present.
>
> cheers,
> severin
>
>
> Larry Meadors wrote:
> > update foo set bar = #newBar# where foo = #foo# and bar = #bar#
> >
> > Larry
> >
> >
> > On 7/13/07, Severin Ecker <se...@gmx.at> wrote:
> >> hi all,
> >>
> >> i have a problem (which i think i need to resolve with cursors but maybe
> >> you know another way in ibatis)
> >>
> >> let's say i have a table
> >> (
> >> hello VARCHAR,
> >> world VARCHAR
> >> )
> >>
> >> both fields form the primary key together.
> >>
> >> what i need is, selecting one specific entry (by specifying values for
> >> both fields in a query). i then want to update one of the two fields
> >> with a new value.
> >>
> >> now i can lock the entry by starting a transaction and selecting my
> >> desired row with a select 'bla' for update. but how do i guarantuee that
> >> exactly that row that was returned is altered with the following update
> >> statement?
> >> can i get some id for the returned row, or tell the sqlmapper to update
> >> the one previously returned in the transaction... or can i just use 2
> >> parameters (or a list) in the sqlmap?
> >>
> >> thanks for help!
> >>
> >> cheeers,
> >> severin
> >>
> >>
> >
>
Re: cursor or multiple parameters
Posted by Koka Kiknadze <22...@gmail.com>.
Well, if you want to do it with domain object, think yuo can do it with
three sql statements instead of two - Select .... for update returning you
domain object. Delete the row using selected object. Change the desired
field of the object and run insert. Of course it will work unless your
table has some cascading deletes or updates.
Re: cursor or multiple parameters
Posted by Severin Ecker <se...@gmx.at>.
hi Larry,
yes or course, but there i need either a new type that i hand over to
the sqlmap unless i can use more than one parameters.. or a list.
your solution (which is the one i'm using atm) requires another type
definiton since i can't use the domain object
public class Bla { String hello; String world; //rest omitted }
where the 'new value' field is not present.
cheers,
severin
Larry Meadors wrote:
> update foo set bar = #newBar# where foo = #foo# and bar = #bar#
>
> Larry
>
>
> On 7/13/07, Severin Ecker <se...@gmx.at> wrote:
>> hi all,
>>
>> i have a problem (which i think i need to resolve with cursors but maybe
>> you know another way in ibatis)
>>
>> let's say i have a table
>> (
>> hello VARCHAR,
>> world VARCHAR
>> )
>>
>> both fields form the primary key together.
>>
>> what i need is, selecting one specific entry (by specifying values for
>> both fields in a query). i then want to update one of the two fields
>> with a new value.
>>
>> now i can lock the entry by starting a transaction and selecting my
>> desired row with a select 'bla' for update. but how do i guarantuee that
>> exactly that row that was returned is altered with the following update
>> statement?
>> can i get some id for the returned row, or tell the sqlmapper to update
>> the one previously returned in the transaction... or can i just use 2
>> parameters (or a list) in the sqlmap?
>>
>> thanks for help!
>>
>> cheeers,
>> severin
>>
>>
>
Re: cursor or multiple parameters
Posted by Larry Meadors <lm...@apache.org>.
update foo set bar = #newBar# where foo = #foo# and bar = #bar#
Larry
On 7/13/07, Severin Ecker <se...@gmx.at> wrote:
> hi all,
>
> i have a problem (which i think i need to resolve with cursors but maybe
> you know another way in ibatis)
>
> let's say i have a table
> (
> hello VARCHAR,
> world VARCHAR
> )
>
> both fields form the primary key together.
>
> what i need is, selecting one specific entry (by specifying values for
> both fields in a query). i then want to update one of the two fields
> with a new value.
>
> now i can lock the entry by starting a transaction and selecting my
> desired row with a select 'bla' for update. but how do i guarantuee that
> exactly that row that was returned is altered with the following update
> statement?
> can i get some id for the returned row, or tell the sqlmapper to update
> the one previously returned in the transaction... or can i just use 2
> parameters (or a list) in the sqlmap?
>
> thanks for help!
>
> cheeers,
> severin
>
>
cursor or multiple parameters
Posted by Severin Ecker <se...@gmx.at>.
hi all,
i have a problem (which i think i need to resolve with cursors but maybe
you know another way in ibatis)
let's say i have a table
(
hello VARCHAR,
world VARCHAR
)
both fields form the primary key together.
what i need is, selecting one specific entry (by specifying values for
both fields in a query). i then want to update one of the two fields
with a new value.
now i can lock the entry by starting a transaction and selecting my
desired row with a select 'bla' for update. but how do i guarantuee that
exactly that row that was returned is altered with the following update
statement?
can i get some id for the returned row, or tell the sqlmapper to update
the one previously returned in the transaction... or can i just use 2
parameters (or a list) in the sqlmap?
thanks for help!
cheeers,
severin
Re: ORA-01795: maximum number of expressions in a list
Posted by indone <bh...@yahoo.com>.
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 <bh...@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.
Re: ORA-01795: maximum number of expressions in a list
Posted by Larry Meadors <lm...@apache.org>.
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 <bh...@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.
>
>