Problem with SP and Foreach statment 1

[XaRz]

Hey dudes,

I'm new to Informix..any tip why this is not working?

CREATE PROCEDURE Phtotgraph_Summary_SP(photograph_classification LIKE fotografs.classificacio_fotograf)
RETURNING CHAR(100) as Photograph, INTEGER as total_Expositions, CHAR(50) as record_Exposition, INTEGER as num_photos


DEFINE photographCode varchar(8);
DEFINE photographName varchar(100);
DEFINE totalExpositions int;
DEFINE averagePhotos decimal(5,2);
DEFINE recordExposition  varchar(50);
DEFINE numPhotos  int;
DEFINE recordExpositionCode varchar(10);
DEFINE rowcount int;


FOREACH cursor
		FOR SELECT  ph.passaport,ph.nom, count(distinct ex.codi_exposicio) INTO photographCode,photographName, totalExpositions
		FROM fotografs ph, exposen ex
		where ph.passaport = ex.passaport 
		group by ph.passaport, ph.nom       					
	    
        rowcount=0;
        FOREACH cursor2 FOR 
            Select max(ex.num_fotos) INTO numPhotos from exposen ex, Exposicions exp , fotografs ph
				where ex.codi_exposicio = exp.codi_exposicio and ph.passaport = ex.passaport and ph.passaport = photographCode;
            rowcount=rowcount+1;
            select exp.titol INTO recordExposition from  exposen ex, Exposicions exp , fotografs ph
					where ex.codi_exposicio = exp.codi_exposicio and ph.passaport = ex.passaport and ph.passaport = photographCode
                    group by exp.titol
                    having max(ex.num_fotos)= numPhotos; 
       END FOREACH;
       IF rowcount>1 THEN
              recordExposition = 'Gamarus';
              CONTINUE FOREACH;
       ELSE
              recordExposition= recordExposition;
       END IF;
	RETURN photographName, totalExpositions,recordExposition,numPhotos WITH RESUME;
    END FOREACH;
END PROCEDURE;

Thanks in advance.

 

[olded]

I don't think the Informix Stored Procedure language requires a cursor name. Try something like this:

FOREACH 
    SELECT  ph.passaport,ph.nom, count(distinct ex.codi_exposicio) INTO photographCode,photographName, totalExpositions