Problem mit stored Procedure und group by

[marabu]

Zitat von shmia:

Wenn du die Klausel SELECT ..... INTO new_table verwendest, dann gibt die Abfrage kein Ergebnis zurück, sondern die Ergebnismenge wird in eine neue physikalische Tabelle kopiert.

Laut SQL Referenz kann ich mit SELECT INTO nur Variablen befüllen. Zum Befüllen einer Tabelle muss INSERT INTO SELECT verwendet werden.

marabu

[shmia]

Zitat von marabu:

Laut SQL Referenz kann ich mit SELECT INTO nur Variablen befüllen. Zum Befüllen einer Tabelle muss INSERT INTO SELECT verwendet werden.

Da hast du natürlich recht; ich habe mich an die Doku vom M$ SQL Server gehalten.
Wenn man bei Firebird mit SELECT .... INTO Variablen befüllt, dann sollte natürlich die Anzahl der Spalten bei SELECT mit der Anzahl der angegebenen Variablen übereinstimmen.
Ich könnte mir auch vorstellen, dass bei SELECT INTO nur ein Datensatz von SELECT geliefert werden darf, da eine Variable nur einen Wert annehmen kann.

[marabu]

Hallo Andreas,

Zitat von shmia:

Ich könnte mir auch vorstellen, dass bei SELECT INTO nur ein Datensatz von SELECT geliefert werden darf, da eine Variable nur einen Wert annehmen kann.

korrekt - nichts anderes habe ich mit singleton select ausdrücken wollen - manchmal fehlen mir scheinbar die klaren Worte...

Freundliche Grüße vom marabu

[Hansa]

Wie shmia das gesagt hat, so wäre es genau richtig. Mir gelingt es leider immer noch nicht, sofern ein SUM usw. im Select drin steht, noch ein anderes Feld dazuzupacken. Wer will kann sich die DB mal ansehen oder das Script ansehen. Also in IBExpert DB registrieren oder das Script ausführen.

Hier ist anscheinend jede Erweiterung verboten. Nicht mal ohne Erweiterung ? SQL, FDB alles verboten ?? .1 und .2 geht auch nicht. Na das ist aber doch jetzt schwer übertrieben !

SQL-Code:

/******************************************************************************/
/****         Generated by IBExpert 2005.06.07 29.08.2005 20:50:59         ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'C:\db\TESTDB.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR GEN_ART_ID;
SET GENERATOR GEN_ART_ID TO 2;

CREATE GENERATOR GEN_KSTAT_ID;
SET GENERATOR GEN_KSTAT_ID TO 6;



SET TERM ^ ; 



/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/

CREATE PROCEDURE TESTSP (
    VONMONAT INTEGER,
    BISMONAT INTEGER,
    ID_ART INTEGER)
RETURNS (
    MONATSMENGE INTEGER)
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE ART (
    ID   INTEGER NOT NULL,
    NR   INTEGER,
    BEZ  CHAR(30)
);


CREATE TABLE KSTAT (
    ID      INTEGER NOT NULL,
    ID_ART  INTEGER,
    MONAT   INTEGER,
    MENGE   INTEGER
);




/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE ART ADD CONSTRAINT PK_ART PRIMARY KEY (ID);
ALTER TABLE KSTAT ADD CONSTRAINT PK_KSTAT PRIMARY KEY (ID);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE KSTAT ADD CONSTRAINT FK_KSTAT_1 FOREIGN KEY (ID_ART) REFERENCES ART (ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: ART_BI0 */
CREATE TRIGGER ART_BI0 FOR ART
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.ID is null) then
    new.ID = gen_id(GEN_ART_ID, 1);
end
^


/* Trigger: KSTAT_BI0 */
CREATE TRIGGER KSTAT_BI0 FOR KSTAT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.ID is null) then
    new.ID = gen_id(GEN_KSTAT_ID, 1);
end
^


SET TERM ; ^



/******************************************************************************/
/****                          Stored Procedures                           ****/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE TESTSP (
    VONMONAT INTEGER,
    BISMONAT INTEGER,
    ID_ART INTEGER)
RETURNS (
    MONATSMENGE INTEGER)
AS
DECLARE VARIABLE MENGE INTEGER;
begin
  SELECT SUM(MENGE) /*,MENGE */ FROM KSTAT WHERE (ID_ART = :ID_ART) AND
            (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT)
            INTO :MONATSMENGE /*GROUP BY MENGE*/;
  suspend;
end
^


SET TERM ; ^

Da ist sogar der Join jetzt raus.

[alex517]

Hallo hansa,

ich bin mir zwar nicht ganz sicher was du als Ergebnis sehen möchtes aber..

Zitat:

begin
SELECT SUM(MENGE) /*,MENGE */ FROM KSTAT WHERE (ID_ART = :ID_ART) AND
(MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT)
INTO :MONATSMENGE /*GROUP BY MENGE*/;
suspend;
end

in diesem Fall nach der Menge zu gruppieren ist nicht sinnvoll.
Ich denke nach Monat wäre das Richtige.
Das Problem ist aber, dass "GROUP BY" mehrere Gruppen (Datensätze) als Ergebnis erzeugen kann.
Dein Statement aber nur ein singleton-Ergebnis zuläßt.
Für meherer Ergebissätze mußt du das SELECT in ein FOR ..DO
packen.

SQL-Code:

CREATE PROCEDURE TESTSP ( VONMONAT INTEGER, BISMONAT INTEGER, ID_ART INTEGER)
RETURNS ( MONAT INTEGER, MONATSMENGE INTEGER)
AS
DECLARE VARIABLE MENGE INTEGER;
begin 
  FOR
    SELECT
      MONAT, SUM(MENGE)
    FROM
      KSTAT
    WHERE
      (ID_ART = :ID_ART) AND (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT)
    group by
      Monat
    INTO
      :Monat, :MONATSMENGE
  DO
    suspend;
end

Verwenden könnte man es z.B. so:

SQL-Code:

select A.*, T.MONAT, T.MONATSMENGE
from art A
LEFT join TESTSP(:VonMonat,:BisMonat, A.ID) T on (1=1)
where
  A.NR=2

alex

[Hansa]

Hi Alex,

Shmias Code wäre für mich schon am sinnvollsten von der Logik her zumindest. Auf das FOR SELECT bin ich auch gestoßen, da war allerdings ein ähnlicher Effekt zu bemerken. SPs kommen leider überall viel zu kurz. Für das gepostete Beispiel-Script wäre ein besserer Vergleich die ID_ART gewesen (statt der Menge), denn da ist letztenendes ein Join über die Artikel vorgesehen (siehe Thread Anfang).

Im Anhang ist das Ergebnis. Nochmals zur Erklärung : geliefert werden 3 Zeilen. Habe von Monat 1-3 eingegeben. Ich bräuchte jetzt eine Zeile mit Wert MONATSMENGE : 9, also 2+3+4 Also nicht die Menge pro Monat, sondern die Gesamtmenge.

[alex517]

wenn du je Artikel aus der Tabelle ART nur eine Gesamtmenge
benötigst, hattest du eigentlich bereits die Lösung mit deiner SP.
Für die Summe über alles (das SELECT darf dann nur Aggregat-Funktionen beinhalten) wird keine GROUP BY benötigt. Ohne GROUP BY wird auch immer nur ein Datensatz in der Ergebnismenge zurückgegeben.
Damit wird FOR..DO.. überflüssig.
Es fehlte nur der richtige Aufruf der SP.

Hier nochmal die SP für die Gesamtmengen (ich habe MONATSMENGE in GESMENGE umbenannt)

SQL-Code:

CREATE PROCEDURE TESTSP_GES (
    VONMONAT INTEGER,
    BISMONAT INTEGER,
    ID_ART INTEGER)
RETURNS (
    GESMENGE INTEGER)
AS
begin
    SELECT
      SUM(MENGE)
    FROM
      KSTAT
    WHERE
      (ID_ART = :ID_ART) AND (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT)
    INTO
      :GESMENGE;
    IF (GESMENGE is NULL) THEN GESMENGE = 0; 
    suspend;
end

und der Aufruf

SQL-Code:

select A.NR, A.BEZ, T.GESMENGE
from art A
left join TESTSP_GES(:VonMonat,:BisMonat, A.ID) T on (1=1)

das Ganze könne man aber auch damit erreichen

SQL-Code:

SELECT A.NR, A.BEZ, COALESCE(SUM(K.MENGE),0)
from
  ART A
  left join KSTAT K on (K.ID_ART=A.ID)
GROUP BY
  1, 2

wichtig ist nur, daß in GROUP BY alle Nicht-Aggregat-Felder enthalten sind.

alex

[Hansa]

Thx, Alex

So gehts ! War allerdings echt schwere Geburt. 8) Baue jetzt mal die Original-DB um.

[Hansa]

Original SP geht auch, bis auf das hier : HAVING (SUM (UMSATZ) <> NULL) Mit "0" geht das zwar, aber ich habe gesehen, daß da doch ziemlich viele NULL Werte drin sind. Wie grenze ich denn die jetzt noch aus ?

Edit : es muß ja zumindest HAVING (NOT SUM (UMSATZ) IS NULL) heißen oder so ähnlich. Wie aber genau ? Wo muß das NOT jetzt hin ? Das Problem ist, daß ich 5 Felder habe und eine Anzeuge nicht erscheinen soll, sofern alle 5 NULL oder 0 sind.

HAVING NOT ((SUM (MENGE) IS NULL) OR (SUM (UMSATZ) IS NULL)) OR ... Oder ist da ein logischer Denkfehler drin ?

[Hansa]

Weiß keiner mehr was außer Alex ? Ich hänge nämlich wieder fest.

So was wie hier geht mit IBExpert einwandfrei:

SQL-Code:

select A.NR, A.BEZ, T.GESMENGE 
from art A 
left join TESTSP_GES(:VonMonat,:BisMonat, A.ID) T on (1=1)

Aber wie greife ich denn jetzt mit Delphi darauf zu ?