FOR..DO in Stored Procedure

[Uwe Merckens]

Hallo Gemeinde,

folgende SP gibt "multiple rows in a singleton select" zurück:
CREATE PROCEDURE GETWEATHERDATA
returns (
minimal numeric(15,2),
maximal numeric(15,2),
durchschnitt numeric(15,2))
as
declare variable suchmon integer;
BEGIN
SELECT DISTINCT EXTRACT(MONTH FROM CAST(DATUM AS DATE)) FROM TEMPERATUR INTO SUCHMON;
FOR
SELECT CAST(ROUND(MIN(HOEHE4), 1) AS FLOAT) AS MINIMAL,
CAST(ROUND(MAX(HOEHE4),1) AS FLOAT) AS MAXIMAL,
CAST(ROUND(AVG(HOEHE4), 1) AS FLOAT) AS DURCHSCHNITT FROM TEMPERATUR
WHERE EXTRACT(MONTH FROM CAST(DATUM AS DATE)) = :SuchMon
into Minimal, Maximal, Durchschnitt
DO
SUSPEND;
END^
Die erste Abfrage soll die Monate aus der Tabelle extrahieren und in suchMon schreiben, die dann das Kriterium für die zweite Abfrage darstellt.

Wer weiß wie es richtig geht?

Danke!
Uwe

[Uwe Merckens]

Do..for

Es muß heissen:
REATE PROCEDURE GETWEATHERDATA
returns (
minimal numeric(15,2),
maximal numeric(15,2),
durchschnitt numeric(15,2))
as
declare variable suchmon integer;
BEGIN
FOR
SELECT DISTINCT EXTRACT(MONTH FROM CAST(DATUM AS DATE)) FROM TEMPERATUR INTO SUCHMON
DO
BEGIN
SELECT CAST(ROUND(MIN(HOEHE4), 1) AS FLOAT) AS MINIMAL,
CAST(ROUND(MAX(HOEHE4),1) AS FLOAT) AS MAXIMAL,
CAST(ROUND(AVG(HOEHE4), 1) AS FLOAT) AS DURCHSCHNITT FROM TEMPERATUR
WHERE EXTRACT(MONTH FROM CAST(DATUM AS DATE)) = :SuchMon
into Minimal, Maximal, Durchschnitt;
SUSPEND;
END
END^