Problem mit stored Procedure und group by

[Hansa]

Hi,

ich bin hier mit einer Statistik beschäftigt. Es sollen die Mengen eines Kunden über ein paar Monate addiert werden. Diese sollen zusammen mit anderen Informationen ausgegeben werden. Ich habe das jetzt mal so reduziert :

SQL-Code:

SELECT SUM(K.MENGE), A.NR
FROM ART A RIGHT JOIN KSTAT K ON (A.ID = K.ID_ART)
WHERE (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT) AND (JAHR = :JAHR)
INTO MONATSMENGE;

Das Problem ist das A.NR (für Artikelnummer). Es heißt, das wäre weder in einer aggregate function noch in einer group by clause vorhanden. SUM usw. brauche ich ja auch dafür nicht. Aber wo muß das GROUP BY genau hin ? Auf Dataset Ebene geht alles, aber in Delphi ist das komplette Statement fast eine DIN A 4 Seite. Ich will zumindest die Sachen, die nur die DB betreffen in die SP verlagern.

[marabu]

Hallo Hansa,

laut SQL Reference gehört "GROUP BY A.Nr" hinter deine INTO-Klausel.

Grüße vom marabu

[Jens Schumann]

Hallo Hanso,
probiere es mal so:

SQL-Code:

SELECT SUM(K.MENGE), A.NR
FROM ART A RIGHT JOIN KSTAT K ON (A.ID = K.ID_ART)
WHERE (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT) AND (JAHR = :JAHR) GROUP BY A.NR 
INTO MONATSMENGE;

evt hilft es auch wenn Du anstatt SUM(K.MENGE) lieber SUM(K.MENGE) AS Feldname schreibst.
Feldname sollte dabei dem Ziel Feld in der Tabelle MONATSMENGE entsprechen.

[Hansa]

hinter dem Into bringt : "Parsing error"

davor bringt : "count of column list and variable list do not match."

AS bringt auch keine Änderung.

@Jens : Monatsmenge ist keine Tabelle, sondern ein Rückgabewert ! Odder liegt es daran ? Ich befürchte langsam, daß das ganz anders gemacht werden muß. 8)

[dfried]

Muss das nicht so aussehen?

SQL-Code:

SELECT SUM(K.MENGE), A.NR INTO MONATSMENGE
FROM ART A RIGHT JOIN KSTAT K ON (A.ID = K.ID_ART)
WHERE (MONAT >= :VONMONAT) 
AND (MONAT <= :BISMONAT) 
AND (JAHR = :JAHR) 
GROUP BY A.NR;

Bei ORACLE muss das INTO zumindest immer nach dem Select kommen.

Gruß
Daniel

[Robert_G]

Zitat:

Zitat von dfried

Bei ORACLE muss das INTO zumindest immer nach dem Select kommen.

Marabu hat es doch schon gelöst. ;)
Bei IB/FB kommt das Into hinter WHERE/ORDER BY clause aber vor die Gruppierung (solange ich es richtig in Erinnerung habe).

[Hansa]

Er hat es nicht gelöst. Das war das erste, was ich vorher schon gemacht habe. Allerdings muß es laut SQL-Referenz davor, wie ens schreibt ! Ergebnis : siehe oben. 8)

[Jens Schumann]

Hallo Hansa,
kann MONATSMENGE 2 Werte aufnehmen???

[marabu]

Hansa, so wird das nie was.

Zitat:

SELECT [TRANSACTION transaction]
[DISTINCT | ALL]
{* | <val> [, <val> …]}
[INTO :var [, :var …]]
FROM <tableref> [, <tableref> …]
[WHERE <search_condition>]
[GROUP BY col [COLLATE collation] [, col [COLLATE collation] …]
[HAVING <search_condition>]
[UNION <select_expr> [ALL]]
[PLAN <plan_expr>]
[ORDER BY <order_list>]
[FOR UPDATE [OF col [, col …]]];

Schon dein erster Code in diesem thread macht kein singleton select. Ich hatte vorausgesetzt, dass dein Code wenigstens als Hypothese taugt.

Grüße vom marabu

[shmia]

ALSO:
wenn du in einer SQL-Abfrage eine Aggregat-Funktion benützt (SUM, MIN, MAX, COUNT, AVG), dann werden mehrere Zeilen zusammengefasst.

SQL-Code:

SELECT SUM(K.MENGE)
FROM ART A RIGHT JOIN KSTAT K ON (A.ID = K.ID_ART) 
WHERE (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT) AND (JAHR = :JAHR)

Hier sind die Felder MONAT & JAHR nicht eindeutig, da kein Tabellenname angegeben. Daher:

SQL-Code:

SELECT SUM(K.MENGE)
FROM ART A RIGHT JOIN KSTAT K ON (A.ID = K.ID_ART) 
WHERE (K.MONAT >= :VONMONAT) AND (K.MONAT <= :BISMONAT) AND (K.JAHR = :JAHR)

Wenn in der Feldliste dann neben der Aggregat-Funktion ein (oder mehrere) "normales" Feld(er) auftaucht, dann muss diese Feld hinter GROUP BY stehen!!

SQL-Code:

SELECT SUM(K.MENGE), A.NR
FROM ART A RIGHT JOIN KSTAT K ON (A.ID = K.ID_ART) 
WHERE (K.MONAT >= :VONMONAT) AND (K.MONAT <= :BISMONAT) AND (K.JAHR = :JAHR)
GROUP BY A.NR

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.

Du kannst auch die Daten mit einer Unterabfrage aufbereiten.
Zuerst die innere SELECT - Abfrage:

SQL-Code:

(SELECT ID_ART, SUM(MENGE) as SUMMENGE FROM KSTAT
WHERE (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT) AND (JAHR = :JAHR)
GROUP BY ID_ART)

Diese Unterabfrage lässt sich nun mit der Tabelle ART verjoinen:

SQL-Code:

SELECT A.*, DevTbl.SUMMENGE
FROM ART A RIGHT JOIN 
   (SELECT ID_ART, SUM(MENGE) as SUMMENGE FROM KSTAT
   WHERE (MONAT >= :VONMONAT) AND (MONAT <= :BISMONAT) AND (JAHR = :JAHR)
   GROUP BY ID_ART) DevTbl

Die Tabelle DevTbl ist der Alias für die Unterabfrage.
Vorteil: du hast Zugriff auf alle Felder der Tabelle ART.
Es gibt also zwei Möglichkeiten:
* zuerst einen JOIN bilden and dann mit GROUP BY verdichten
* zuerst mit GROUP BY verdichten (die Unterabfrage) und dann den JOIN bilden

Ich hoffe, das bringt etwas Licht ins Dunkel :angel2:

[marabu]

Zitat:

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:

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:

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 ?? :shock: .1 und .2 geht auch nicht. Na das ist aber doch jetzt schwer übertrieben ! :evil:

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 ! :thumb: War allerdings echt schwere Geburt. 8) Baue jetzt mal die Original-DB um.

[Hansa]

Original SP geht auch, bis auf das hier :

SQL-Code:

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 ? :shock:

Edit : es muß ja zumindest

SQL-Code:

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.

SQL-Code:

HAVING NOT ((SUM (MENGE) IS NULL) OR (SUM (UMSATZ) IS NULL)) OR ...

Oder ist da ein logischer Denkfehler drin ? :gruebel:

[Hansa]

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

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 ?

[alex517]

Hi Hansa,

Zitat:

SQL-Code:

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 ?

Wie lautet das ganze SQL-Statement? "UMSATZ" ist bisher noch nicht aufgetaucht.
Sollte es aber um die GESMENGE gehen, so kann diese NULL nicht enthalten, wenn man in der SP vor SUSPEND

SQL-Code:

    IF (GESMENGE is NULL) THEN GESMENGE = 0;

einfügt. Das kann man auch immer einfach auf ..WHERE GESMENGE>0 prüfen.

Zitat:

Aber wie greife ich denn jetzt mit Delphi darauf zu ?

wo ist den das Problem?

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
{ FIBDataSet1.SQLs.SelectSQL:
    select A.NR, A.BEZ, T.GESMENGE
    from art A
    left join TESTSP_GES(:VonMonat,:BisMonat, A.ID) T on (1=1)
    @@Filter@
}
begin
  FIBDataSet1.Close;
  FIBDataSet1.ParamByName('VonMonat').AsInteger := StrToIntDef(EVon.Text, 0);
  FIBDataSet1.ParamByName('BisMonat').AsInteger := StrToIntDef(EBis.Text, 999999);
  if CBOhne0Mengen.Checked then
    FIBDataSet1.ParamByName('Filter').AsString := 'where GESMENGE>0'
  else
    FIBDataSet1.ParamByName('Filter').AsString := '';
  FIBDataSet1.open;
end;

alex

[Hansa]

Vor dem Suspend das NULL auswerten ? Oh je, da hätte ich auch selber draufkommen können. 8) Nun gut, das geht natürlich jetzt auch. Aber wo zum Teufel kommen die ganzen Klammeraffen da her ? :shock: Bzw., was machen die ? Die Checkboxen kommen natürlich auch noch ins Spiel und nicht nur eine ! Aber es wird sowieso immer komplizierter. Konkret geht es um 5 SUM-Felder, eines ist das SUM (UMSATZ) und da wirds jetzt richtig kompliziert. Wie baue ich jetzt da noch eventuell eine Mwst.-SP ein, die den Brutto Umsatz errechnet ? Nur so als Vorschau. 8)

[alex517]

Zitat:

Zitat von Hansa

Aber wo zum Teufel kommen die ganzen Klammeraffen da her? :shock: Bzw., was machen die ?

Sieh doch mal in der Hilfe zu FIBPlus unter Macros nach.
Damit kann man in einem FIBDataSet z.B. bequem WHERE, ORDER oder sonstige Klauseln setzen, ändern und auch wieder löschen.

Zitat:

Zitat von Hansa

Wie baue ich jetzt da noch eventuell eine Mwst.-SP ein, die den Brutto Umsatz errechnet ? Nur so als Vorschau. 8)

Etwa die SP von Letztens, mit den MwSt-Kennzeichen? :pale: Holen mich jetzt etwa meine unbedachten Äußerungen wieder ein? :wink:
alex

[Hansa]

Ja, ich habe tatsächlich einige Wechselstaben verbuchtelt, oder wie das heißt. :mrgreen: Zu den Klammeraffen : da ist eine sagenhafte fast nichtssagende Seite über Macro zu finden. Wieso sind da 2 @ und hinten noch einer ? Laut dieser Seite müßte es doch reichen nur @Filter zu schreiben ? Und dann das % usw. wozu ist das gut ? Das ganze paßt irgendwie nicht so richtig in die Firebird-Syntax. Werde wohl mal den SQL-Monitor anschmeißen.

[alex517]

Die Macros sind eine reine FIBPlus-Funktionalität, hat also nichts mit Firebird zu tun.
Ich gebe zu es steht nicht viel in der FibPlus53.pdf und außerdem haben ich es mir auch erklären lassen.

Das Macro fängt mit (@@) an und endet mit (@).
Hinter dem (%) kann man den Default-Wert eintragen.
Dieser kommt zu Geltung wenn das Marco nicht über ParamByName gesetzt wird.
(#) setzt man wenn der Ausdruck eigentlich in ('..') stehen muß zB. bei Strings oder Datumswerten.

where DatumVon>=@@Datum%#01.01.1980@

FibPlus macht daraus:

a) wenn Marco nicht gesetzt wird:
where DatumVon>='01.01.1980'

b) wenn Marco gesetzt wird mit FibDataSet.ParamByName('DatumVon').AsDateTime = StrToDate('25.05.2005')
where DatumVon>='25.05.2005'

Ich setze Macros aber bisher nur in der einfachen Form ein.

alex