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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
>> > Sent: Thursday, June 28, 2007 6:21 AM
>> > To: [email protected]
>> > 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.
