Hi Diego,

> The original query had this condition
>
> AND OC.MSISDN LIKE '6________'
>
> and you have replaced it with
>
> AND OC.MSISDN STARTING WITH '6'
> AND substring(OC.MSISDN,9,1)<>''
> AND substring(OC.MSISDN,10,1)=''
>
> The query performs better with the conditions the way you have

coded

> them?

LIKE '6_______' doesn't use index if i remember correctly of FB,
because of wildchars.
STARTING WITH forces engine to use index.
substring scans and works only on rows that starting with '6'.
sorry about substring syntax, it should be like as substring(MSISDN
from 9 for 1).

>
> I think the way you have coded the query, it doesn't return the

same

> results, because max INTERACT_ID for a particular MSISDN could not

be

> LEVEL 1. I want to count rows for a MSISDN if they are the max
> interaction, but only if the last is LEVEL 1. For example
>

it was my fault, sorry me.
i must to be more careful while reversengineering. ;)

pass the level 0 rows and
suspend only the LEVEL 1 rows when you write SP.
...
FOR
SELECT MSISDN,LEVEL,count(*)
FROM OPPORTUNITY_CALLS
WHERE OPP_DATE BETWEEN :DATEMIN AND :DATEMAX
AND MSISDN STARTING WITH '6'
AND substring(MSISDN,9,1)<>''
AND substring(MSISDN,10,1)=''
GROUP BY MSISDN,LEVEL
do
begin
--check the MSISDN and LEVEL fields changed and obeys your condition
if it is then suspend the record else do nothing(do next fetch)
end
...

select count(*) from myproc('2005-10-01','2005-10-10')

Regards.
Ali