Hi Diego,

Now, after a shower i understand your query perfectly.(whether?) :))

you need all rows if their level=1 and inretact_id is minimum for
same MSISDN. ok?

>SELECT COUNT (OC.MSISDN)
> FROM OPPORTUNITY_CALLS OC
> WHERE OC.OPP_DATE >= '2005-10-01' AND OC.OPP_DATE <= '2005-10-10'
> AND OC.MSISDN LIKE '6________'
> AND OC.LEVEL = 1
> AND NOT EXISTS(
> SELECT 1
> FROM OPPORTUNITY_CALLS OC2
> WHERE OC.MSISDN=OC2.MSISDN
> AND OC2.OPP_DATE >= '2005-10-01' AND OC2.OPP_DATE <= '2005-10-10'
> AND OC.INTERACT_ID < OC2.INTERACT_ID
> )

create procedure level1_counter(datemin date, datemax date)
returns(numberofrows integer)
as

declare prev_msisdn varchar(9);
declare msisdn varchar(9);
declare level smallint;
declare interact_id integer;
declare min_interact_id integer;

begin

prev_msisdn='';
min_interact_id=999999999;
numberofrows=0;

FOR
SELECT MSISDN,LEVEL,interact_id
FROM OPPORTUNITY_CALLS
WHERE OPP_DATE BETWEEN :DATEMIN AND :DATEMAX
AND MSISDN STARTING WITH '6'
AND substring(MSISDN,9,1)<>'' -- if all 9 chars used
-- AND substring(MSISDN,10,1)='' no need, msisdn allready 9 chars
ORDER BY MSISDN -- msisdn index will help to this order, there is
no grouping, sorting etc..
INTO :msisdn,:level,:interact_id
do
begin

-- count your rows here with some compare and assigning
-- if the min_interact_id is at level1 for the same msisdn then
numberofrows=numberofrows+1

end

-- check again for last row

suspend; -- return numberofrows

end


select * from level1_counter('2005-10-01','2005-10-10')

Regards.

Ali