Problem with SP and Foreach statment
Problem with SP and Foreach statment
(OP)
Hey dudes,
I'm new to Informix..any tip why this is not working?
Thanks in advance.
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;
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.
RE: Problem with SP and Foreach statment
I don't think the Informix Stored Procedure language requires a cursor name. Try something like this:
CODE
SELECT ph.passaport,ph.nom, count(distinct ex.codi_exposicio) INTO photographCode,photographName, totalExpositions