BCD-Überlauf bei Felder mit NULL

[GroHae]

Hallo zusammen,

Ich greife via DBX auf meine MSSQL Tabellen zu

Delphi-Quellcode:

    MyConnection : TDBXConnection;  // Die DB Verbindung (wird übergeben)
    MyCommand : TDBXCommand; // Der Befehl (wird erstellt)
    MyReader : TDBXReader;  // Der Datensatzzeiger (wird erstellt)

Ich habe schon öfters Probleme gehabt, wenn ich auf Tabellen zugreife und Werte in der Tabelle den Wert NULL haben.
Heute wieder.

Ich will folgenden Befehl abschicken:

Delphi-Quellcode:

procedure TTblVkUmsatzMaWi_FV.SelGruppiertAllesEinesMonats(iUnternehmen, iMonat, iJahr: integer);
begin
  with MyCommand do
  begin
    Text := 'SELECT ' +
      'SUM(BetragMW) AS Sum_BetragMW, ' +
      'SUM(MengeBasis) AS Sum_MengeBasis, ' +
      'SUM(Nettogewicht) AS Sum_Nettogewicht, ' +
      'SUM(Rohstoffeinsatz) AS Sum_Rohstoffeinsatz, ' +
      'SUM(Verpackungseinsatz) AS Sum_Verpackungseinsatz, ' +
      'SUM(Prozesskosten) AS Sum_Prozesskosten, ' +
      'SUM(Energiekosten) AS Sum_Energiekosten, ' +
      'SUM(Entsorgungsgebuehr) AS Sum_Entsorgungsgebuehr, ' +
      'UnternehmenNr_ID, VerkanDebNr, Nr, Debitorhauptgruppencode, Debitoruntergruppencode, KostentraegerCode' +
      ' FROM '+ GetTblName + ' WHERE ' +
      '(UnternehmenNr_ID = ?) AND ' +
      '(Buchungsdatum BETWEEN ? and ?) AND' +
      '(Belegart <> '''') ' +   // NICHT FRACHTKOSTEN --> RECHNUNG + GUTSCHRIFT
      'GROUP BY UnternehmenNr_ID, VerkanDebNr, Nr, Debitorhauptgruppencode, Debitoruntergruppencode, KostentraegerCode';
    AddParameterInt32(iUnternehmen);
    AddParameterDate(GetVonDatumAusMonatUndJahr(iMonat,iJahr));
    AddParameterDate(GetBisDatumAusMonatUndJahr(iMonat,iJahr));
  end;
  MyReader := MyCommand.ExecuteQuery;
end;

wenn ich dann später die Daten abgreife:

Delphi-Quellcode:

    with MyCommand, iRec do
    begin
      BetragMW := BcdToDouble(MyReader.Value['Sum_BetragMW'].GetBcd);  // Summe!
      MengeBasis := BcdToDouble(MyReader.Value['Sum_MengeBasis'].GetBcd);  // Summe!
      Nettogewicht := BcdToDouble(MyReader.Value['Sum_Nettogewicht'].GetBcd);  // Summe!
      Rohstoffeinsatz := BcdToDouble(MyReader.Value['Sum_Rohstoffeinsatz'].GetBcd);  // Summe!
      Verpackungseinsatz := BcdToDouble(MyReader.Value['Sum_Verpackungseinsatz'].GetBcd);  // Summe!
      Prozesskosten := BcdToDouble(MyReader.Value['Sum_Prozesskosten'].GetBcd);  // Summe!
      Energiekosten := BcdToDouble(MyReader.Value['Sum_Energiekosten'].GetBcd);  // Summe!
      Entsorgungsgebuehr := BcdToDouble(MyReader.Value['Sum_Entsorgungsgebuehr'].GetBcd);  // Summe!
      UnternehmenNr_ID := MyReader.Value['UnternehmenNr_ID'].GetInt32;
      Debitorhauptgruppencode := Trim(MyReader.Value['Debitorhauptgruppencode'].GetAnsiString);
      Debitoruntergruppencode := Trim(MyReader.Value['Debitoruntergruppencode'].GetAnsiString);
      VerkanDebNr := Trim(MyReader.Value['VerkanDebNr'].GetAnsiString);
      KostentraegerCode := Trim(MyReader.Value['KostentraegerCode'].GetAnsiString);
      Nr := Trim(MyReader.Value['Nr'].GetAnsiString);
    end;

bekomme ich bei
Rohstoffeinsatz := BcdToDouble(MyReader.Value['Sum_Rohstoffeinsatz'].GetBcd); // Summe!
eine BCD-überlauf.

Ich bin dann hergegangen und habe auf der SQL Console mit

SQL-Code:

UPDATE    VkUmsatzMaWi_FV
SET              Prozesskosten = 0
WHERE     (UnternehmenNr_ID = 0) AND (Prozesskosten IS NULL)

Alle Felder auf 0 gesetzt welche vorher NULL waren. und danach hat es geklappt.

Aber das ist natürlich keine Lösung!

Weiß jemand wo das Problem liegt?

Grüße

Thomas

[DeddyH]

Ich will mich nicht zu weit aus dem Fenster lehnen, aber das ist AFAIK ein generelles Problem mit Aggregatfunktionen und NULL-Werten. Du könntest das umgehen, indem Du entweder die Felder gleich NOT NULL definierst oder bei der Abfrage Konstrukte wie IIF oder CASE einbaust.

Sollte ich falsch liegen, bitte korrigieren.

[GroHae]

hm..

ob das ein generelles Problem ist weiß ich nicht. Da müsste ich mein SQL Buch fragen, das hab ich aber verliehen bzw muss ich heut Abend zu Hause nachlesen. Ich hatte ja schon ein paar mal das Problem, kann aber jetzt nicht ausschließen, das ich da immer mit Aggregatfunktionen gearbeitet habe. Hab halt immer schnell die Tabelle mit Daten gefüllt und das Problem aus Zeitgründen vor mir hergeschoben.

Mit IF und CASE brauch ich gar nicht erst anfangen. Das wird zu wild...

Das mit NOT NULL könnte gehen, das hast du recht. Muss nur noch meine Kollegen fragen, da die Tabelle von außen gefüllt wird. Nicht das es dort Probleme gibt.

Danke und Grüße

Thomas

[GroHae]

Glaube ich hab es:

ISNULL(SUM(Prozesskosten), 0)

könnte die Lösung sein

[DeddyH]

Sauberer wäre es IMO aber, die Felder NOT NULL DEFAULT 0 zu definieren, dann kommst Du gar nicht erst in die Verlegenheit.

[GroHae]

och nö, das ist doch viel zu einfacht...

*seufz*

:wall:

irgendwo habe ich mal gelesen, mann solle grundsätzlich NULL nicht zulassen.

[DeddyH]

Das ist nun auch wieder zu pauschal. Wenn Du allerdings weißt, dass Du mit den Werten später Berechnungen durchführen willst, würde ich NULL nicht zulassen.

[GroHae]

weil ich gerade 4 Stunden für die Fehlersuche benötigt habe:

Auch wenn die Daten in der Tabelle keine NULL enthalten, habe ich das Problem, dass bei Summen im SQL Befehl Vorsicht ratsam ist:

Ist der Datentyp des Feldes über das man summiert Real würde folgendes fehlerfrei laufen auch wenn ich durch die WHERE Einschränkung keine Daten zurück bekomme

Delphi-Quellcode:

  // SQL Befehl
  MyCommandVorbereitung;
  with MyCommand do
  begin
    Text := 'SELECT SUM(Nettogewicht) AS Sum_Nettogewicht ' +
      ' FROM '+ GetTblName + ' WHERE ' +
      '(UnternehmenNr_ID = ?) AND ' +
      '(Belegdatum BETWEEN ? AND ?) AND ' +
      '(Markenstamm = ?)';
    AddParameterInt32(iUnternehmen);
    AddParameterDate(iVonDatum);
    AddParameterDate(iBisDatum);
    AddParameterString(iMarke);
  end;
  MyCommandExecuteQuery;

  // Daten holen
  ioGewicht := 0;
  while Next do
  begin
    ioGewicht := MyReader.Value['Sum_Nettogewicht'].GetDouble;  // hier wird 0 zurückgegeben wenn KEINE Daten da sind.
  end;

Ist der Datentyp des Feldes über das man summiert Decimal würde folgendes NICHT fehlerfrei laufen wenn ich durch die WHERE Einschränkung keine Daten zurück bekomme

Delphi-Quellcode:

  // SQL Befehl
  MyCommandVorbereitung;
  with MyCommand do
  begin
    Text := 'SELECT SUM(Nettogewicht) AS Sum_Nettogewicht ' +
      ' FROM '+ GetTblName + ' WHERE ' +
      '(UnternehmenNr_ID = ?) AND ' +
      '(Belegdatum BETWEEN ? AND ?) AND ' +
      '(Markenstamm = ?)';
    AddParameterInt32(iUnternehmen);
    AddParameterDate(iVonDatum);
    AddParameterDate(iBisDatum);
    AddParameterString(iMarke);
  end;
  MyCommandExecuteQuery;

  // Daten holen
  ioGewicht := 0;
  while Next do
  begin
    ioGewicht := BcdToDouble(MyReader.Value['Sum_Nettogewicht'].GetBcd);  // Fehler: "BCD-Überlauf" möglich
  end;

In dem Moment wo GetBcd aufgerufen wird bekomme ich die Meldung "BCD-Überlauf"

ich muss also in diesem Fall mit

Delphi-Quellcode:

    Text := 'SELECT ISNULL(SUM(Nettogewicht),0) AS Sum_Nettogewicht, ' +
      'ISNULL(SUM(BetragMW),0) AS Sum_BetragMW FROM '+ GetTblName + ' WHERE ' +
      '(UnternehmenNr_ID = ?) AND ' +
      '(Belegdatum BETWEEN ? AND ?) AND ' +
      '(Markenstamm = ?)';

arbeiten. Dann klapp es immer.


Oder anders gesagt: Nutze immer ISNULL bei Summenberechnung

[nahpets]

Hallo,

Zitat:

Zitat von GroHae

Delphi-Quellcode:

    Text := 'SELECT ISNULL(SUM(Nettogewicht),0) AS Sum_Nettogewicht, ' +
      'ISNULL(SUM(BetragMW),0) AS Sum_BetragMW FROM '+ GetTblName + ' WHERE ' +
      '(UnternehmenNr_ID = ?) AND ' +
      '(Belegdatum BETWEEN ? AND ?) AND ' +
      '(Markenstamm = ?)';

Oder anders gesagt: Nutze immer ISNULL bei Summenberechnung

Dir ist klar, dass bei Deiner Vorgehensweise die ermittelte Summe auf Null abgefragt wird und nicht der Inhalt der einzelnen Zellen der Spalten.

Delphi-Quellcode:

    Text := 'SELECT SUM(ISNULL(Nettogewicht,0)) AS Sum_Nettogewicht, ' +
      'SUM(ISNULL(BetragMW,0)) AS Sum_BetragMW FROM '+ GetTblName + ' WHERE ' +
      '(UnternehmenNr_ID = ?) AND ' +
      '(Belegdatum BETWEEN ? AND ?) AND ' +
      '(Markenstamm = ?)';

Wäre dass nicht eventuell eine Alternative, wenn der Inhalt einer Spalte Null ist, wird 0 geliefert und darüber die Summe gebildet. Wie verhält sich Sum, wenn es in einer Spalte sowohl Zahlen, als auch Null-Werte findet. Mit Deiner Vorgehensweise ersetzt Du die Null-Werte einer leeren Ergebnismenge durch 0, hier wäre es besser abzufragen, ob das SQL ein Ergebnis geliefert hat und nur in diesem Falle die Werte zu verarbeiten.

SQL-Code:

select isnull(sum(Nettogewicht),0) from tabelle where 1 = 2

In diesem Beispiel bekommst Du als Ergebnis bei MSSQL-Server 0, obwohl Du eine leere Ergebnismenge bekommen hast. Du kannst hier also quasi die Inhalte des ersten von keinen Datensätzen abfragen. Das halte ich für "gewöhnungsbedürftig".

SQL-Code:

select sum(isnull(Nettogewicht,0)) from tabelle where 1 = 2

Bei dieser Variante bekommst Du nach wie vor eine leere Ergebnismenge, da ja keine Datensätze von der Abfrage gefunden werden. Je nach Datenbank ist das Ergebnis einer Summierung Null, sofern zwischen vielen Werten nur einer Null ist. Du kommst also an einem Default 0 in der Tabellendefinition oder einem Update mit Set spalte = 0 where spalte is null bei Deiner Vorgehensweise nicht vorbei.

[GroHae]

Hi,

Zitat:

Dir ist klar, dass bei Deiner Vorgehensweise die ermittelte Summe auf Null abgefragt wird und nicht der Inhalt der einzelnen Zellen der Spalten.

ja, bei meinem konkreten Problem ist das so richtig.


Aber du hast recht, den Unterschied zwischen den beiden Varianten

SQL-Code:

select isnull(sum(Nettogewicht),0) from tabelle where 1 = 2

select sum(isnull(Nettogewicht,0)) from tabelle where 1 = 2

hätte ich nicht bedacht.