SELECT   sum(incom) INTO vAmount  
                               FROM saldo s1,account a    
                               WHERE s1.idacc=a.idacc AND s1.currency='980' and 
                               ((a.acc[ 1 , 4 ] in ('2512','2513','2520','2523','2526','2530','2531','2541','2542','2544','2545','2552', 
                               '2554','2555','2560','2561','2562','2565','2600','2601','2602','2603','2604','2605','2606', 
                               '2655','2553','2650') or a.acc[ 1 , 3 ] in ('264','257')  )                                     
                               and a.currency =vCurrency                                                               
                               and s1.dtend = (SELECT MIN(dtend) FROM saldo                                                                  
                               WHERE idacc = s1.idacc AND currency = s1.currency AND dtend >=vDTcalc3 )                         
                               and EXISTS(SELECT * FROM accdate WHERE idacc=a.idacc and status<>'C' and date(accdt)<vDTcalc3)             
                               and NOT EXISTS(SELECT * FROM accdate WHERE idacc=a.idacc and status='C' and date(accdt)<vDTcalc3) )         
                               and s1.incom> 0  and a.idsubunit in ( 0 , 1 ,- 1 );

----------
Procedure: informix.mas_finrep_subunit
select sum(x0.incom ) from "informix".saldo x0 ,"informix".account x1 ,"informix".accdate x2 where ((((((x0.idacc = x1.idacc ) AND (x0.currency =  980  ) ) AND (((((x1.acc [ 1 , 4 ] IN ('2512' ,'2513' ,'2520' ,'2523' ,'2526' ,'2530' ,'2531' ,'2541' ,'2542' ,'2544' ,'2545' ,'2552' ,'2554' ,'2555' ,'2560' ,'2561' ,'2562' ,'2565' ,'2600' ,'2601' ,'2602' ,'2603' ,'2604' ,'2605' ,'2606' ,'2655' ,'2553' ,'2650' )) OR (x1.acc [ 1 , 3 ] IN ('264' ,'257' )) ) AND (x1.currency NOT IN ( 980  , 840  , 978  , 643  , 826  , 756  )) ) AND (x0.dtend = (select min(x3.dtend ) from "informix".saldo x3 where (((x3.idacc = x0.idacc ) AND (x3.currency =  980  ) ) AND (x3.dtend >= ? ) ) ) ) ) AND NOT EXISTS (select x4.idacc ,x4.status ,x4.iduser ,x4.accdt from "informix".accdate x4 where (((x4.idacc = x1.idacc ) AND (x4.status = 'C' ) ) AND (DATE (x4.accdt ) < ? ) ) ) ) ) AND (x0.incom > '0.00' ) ) AND (x1.idsubunit = ? ) ) AND (((x2.idacc = x1.idacc ) AND (x2.status != 'C' ) ) AND (DATE (x2.accdt ) < ? ) ) )

QUERY:
------


Estimated Cost:  24617 
Estimated # of Rows Returned:  1 

   1 ) informix.a: SEQUENTIAL SCAN

        Filters: ((((informix.a.acc[ 1 , 4 ] IN ('2512' , '2513' , '2520' , '2523' , '2526' , '2530' , '2531' , '2541' , '2542' , '2544' , '2545' , '2552' , '2554' , '2555' , '2560' , '2561' , '2562' , '2565' , '2600' , '2601' , '2602' , '2603' , '2604' , '2605' , '2606' , '2655' , '2553' , '2650' )OR informix.a.acc[ 1 , 3 ] IN ('264' , '257' )) AND informix.a.idsubunit = '<VAR>' ) AND informix.a.currency NOT IN ( 980  ,  840  ,  978  ,  643  ,  826  ,  756  )) AND NOT EXISTS <subquery> ) 

   2 ) informix.s1: INDEX PATH

        Filters: ((informix.s1.incom >  0 . 00  AND informix.s1.currency =  980  ) AND informix.s1.dtend = <subquery> ) 

    ( 1 ) Index Keys: idacc 
        Lower Index Filter: informix.s1.idacc = informix.a.idacc 
NESTED LOOP JOIN

   3 ) informix.accdate: INDEX PATH  (First Row)

        Filters: (DATE (informix.accdate.accdt ) < '<VAR>' AND informix.accdate.status != 'C' ) 

    ( 1 ) Index Keys: idacc   (Serial, fragments: ALL)
        Lower Index Filter: informix.accdate.idacc = informix.a.idacc 
NESTED LOOP JOIN  (Semi Join)

    Subquery:
    ---------
    Estimated Cost:  4 
    Estimated # of Rows Returned:  1 

       1 ) informix.saldo: INDEX PATH

        ( 1 ) Index Keys: idacc currency dtend   (Key-Only)  (Aggregate)
            Lower Index Filter: ((informix.saldo.idacc = informix.s1.idacc AND informix.saldo.dtend >= '<VAR>' ) AND informix.saldo.currency =  980  ) 


    Subquery:
    ---------
    Estimated Cost:  1 
    Estimated # of Rows Returned:  1 

       1 ) informix.accdate: INDEX PATH

            Filters: DATE (informix.accdate.accdt ) < '<VAR>' 

        ( 1 ) Index Keys: idacc status 
            Lower Index Filter: (informix.accdate.idacc = informix.a.idacc AND informix.accdate.status = 'C' )