Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with SP and Foreach statment 1

Status
Not open for further replies.

XaRz

Programmer
Jun 17, 2005
34
0
0
ES
Hey dudes,

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

Code:
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.
 

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

Code:
FOREACH 
    SELECT  ph.passaport,ph.nom, count(distinct ex.codi_exposicio) INTO photographCode,photographName, totalExpositions
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top