SQL: Update mit Select und IF/CASE in der Bedingung

[haentschman]

Moin...:P

Ich glaube ich muß mir helfen lassen...:oops:

Was soll passieren:
Eine Spalte in ADR (Tabelle) soll mit der Summe aus den BEL (Tabelle) geupdatet werden. So weit so gut.

Code:

update
  ADR
set
  Value = (select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
                B.Adr = ADR.Adr

Erklärung:
ADR.Adr -> nie leer!
B.Adr -> kann leer sein. In diesem Falle wäre das Kriterium B.KDNR.

Problem:
Die "leeren" Datensätze werden nicht im Update berücksichtigt.

Versuch mit IF (falsch, zeigt aber was ich denke...andere Bedingung):

Code:

update
  ADR
set
  Value = (select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
                IF (B.Adr > '')
                  B.Adr = ADR.Adr
                ELSE
                  B.KDNR = ADR.Adr

Ich habe schon mit CASE experimentiert...ohne Erfolg :oops:

Danke für Infos...:wink:

PS:
Ich hasse solche Verrenkungen...

[TigerLilly]

Schreib doch zu den Felder Aliase dazu, damit man weiß welche Felder aus welcher Tabelle sind.

[haentschman]

OK...:wink:

[rokli]

Moin,

kannst Du ein COALESCE um das SUM machen? Dann bekommst bei nicht vorhandenen Datensätzen die ZIffer 0 zurück und kein NULL und damit sollte der UPDATE erfolgen.

[haentschman]

Danke...

Zitat:

Dann bekommst bei nicht vorhandenen Datensätzen

Die Datensätze sind ja vorhanden, nur das Kriterium für das Update (Zuordnung der Summe zu ADR.Adr) wäre bei diesen Datensätzen ein anderes. Ich möchte alle Datensätze des Subselects mit der Bedingung:
Summe aus:
1. B.Adr = ADR.Adr
+
2. B.KDNR = ADR.Adr

[dataspider]

Reicht da nicht ein OR?

SQL-Code:

update
  ADR
set
  Value = (select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
               ( (B.Adr > '' and B.Adr = ADR.Adr) or
                 (B.KDNR = ADR.Adr)
               )

[Uwe Raabe]

Was spricht denn gegen das hier?

SQL-Code:

(select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
                ((B.Adr IS NOT NULL and B.Adr = ADR.Adr) or
                 (B.Adr IS NULL and B.KDNR = ADR.Adr))

[Uwe Raabe]

Zitat:

Zitat von dataspider (Beitrag 1466698)

Reicht da nicht ein OR?

SQL-Code:

update
  ADR
set
  Value = (select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
               ( (B.Adr > '' and B.Adr = ADR.Adr) or
                 (B.KDNR = ADR.Adr)
               )

Das würde auch diese Fälle auswerfen:

SQL-Code:

  (B.Adr > '' and B.Adr <> ADR.Adr) and (B.KDNR = ADR.Adr)

[haentschman]

Zitat:

Reicht da nicht ein OR?

...leider nicht. :cry: Mit OR kommt etwa in der Summe das Doppelte raus. :?

Code:

(select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
                ((B.Adr IS NOT NULL and B.Adr = ADR.Adr) or
                 (B.Adr IS NULL and B.KDNR = ADR.Adr))

Da fehlen auch die Datensätze mit B.Adr = ''

PS: Das B.Adr ist nicht NULL sondern ''

Test:
Versuch des Zusammenrechnens zweier Subselects (getrennte Bedingungen) zu einem Wert...da kommt was ganz komisches raus.

Zahlen mit Original SQL:
B.Brutto = 1058373,92,
3 Datensätze mit (B.Adr = '') = 1156,01
ADR.SUMME = 1057217,91

[RSF]

Ich hoffe das Problem verstanden zu haben: Null Werte gehen nicht.
Die Null-Felder müssen vor Berechnungen erst auf einen definierten Wert gebracht werden
z.B.

Code:

NULLIF(( Select SUM(Brutto)  From Artikel Where(ID_Nr=did_jumbo)),0);

[Uwe Raabe]

Zitat:

Zitat von haentschman (Beitrag 1466702)

PS: Das B.Adr ist nicht NULL sondern ''

OK, und so (ohne reale Daten kann man das hier nicht testen)?

Code:

(select SUM(B.Brutto) as Summe from BEL B
             where
               (B.Art = 'BLUBB' or B.Art = 'BLA') and
                ((B.Adr > '' and B.Adr = ADR.Adr) or
                 (B.Adr = '' and B.KDNR = ADR.Adr))

[haentschman]

:cry: Nach 5 Minuten habe ich abgebrochen...

Ich mache mal ein SQL mit 2 Tabelle und Daten...


Nachtrag:

Code:

create table ADR
(
  ID int identity(0,1) not null,
  Adr varchar(20),
  AdrValue int
)
create table BEL
(
  ID int identity(0,1) not null,
  Adr varchar(20),
  KDNR varchar(10),
  BelValue int
)
GO
SET IDENTITY_INSERT ADR ON
insert into ADR (ID, Adr, AdrValue) values (1, 'Bla', 0)
insert into ADR (ID, Adr, AdrValue) values (2, 'Blubb', 0)
SET IDENTITY_INSERT ADR OFF

SET IDENTITY_INSERT BEL ON
insert into BEL (ID, Adr, KDNR, BelValue) values (1, 'Bla', '', 1)
insert into BEL (ID, Adr, KDNR, BelValue) values (2, 'Bla', '', 2)
insert into BEL (ID, Adr, KDNR, BelValue) values (3, 'Bla', '', 3)
insert into BEL (ID, Adr, KDNR, BelValue) values (4, '', 'Bla', 4)
insert into BEL (ID, Adr, KDNR, BelValue) values (5, 'Blubb', '', 2)
insert into BEL (ID, Adr, KDNR, BelValue) values (6, 'Blubb', '', 3)
SET IDENTITY_INSERT BEL OFF
GO

Original SQL Bedingung:

Code:

UPDATE
  ADR
SET
  AdrValue = (select SUM(B.BelValue) as Summe from BEL B
                where
             b.Adr = ADR.Adr)

Egebnis:
Bla = 6 -> Erwartet 10 incl. KDNR = Bla
Blubb = 5

[haentschman]

Danke an Alle...8-)

Der Preis geht an Uwe. :cheers: Mit meiner Test DB kommt das erwartete Ergebnis raus. Meiner DB fehlt mit Sicherheit nur ein Index was die Laufzeit erklärt.

Nachtrag:
Das Ergebnis ist korrekt.

DB: 3GB
-> relevante Daten 8000 Datensätze (Update)
* ohne Index (fehlende Felder): nach 5 Minuten abgebrochen
* Index mit allen relevanten Feldern / einzeln oder zusammengesetzt: 22s
* Index mit nur einer Bedingung (wie früher): 1s :shock:


Ideen für Optimierung?

[haentschman]

Moin...:P

Update:
sowohl mit:

Code:

((B.Adr > '' and B.Adr = ADR.Adr) or 
 (B.Adr = '' and B.KDNR = ADR.Adr))),

als auch mit:

Code:

Adr.Adr = case 
  when B.Adr > '' 
  then B.Adr   
  else B.KDNR 
end),

...war die Geschwindigkeit langsam. (trotz aller Indizies und neu erstellt komplett 15min/8000DS) :cry:

Zusammengesetzte Werte aus getrennten SQL: (komplett 2s/8000DS)

Code:

UPDATE
  ADR
SET
  AdrValue = (select SUM(B.BelValue) as Summe from BEL B
                where B.Adr = ADR.Adr) 
              +
             (select SUM(B.BelValue) as Summe from BEL B
                where B.KDNR = ADR.Adr)

Könnte das mir jemand logisch erklären? :gruebel:

[mkinzler]

Beim Case muss ja für jeden Datensatz Berechnungen durchgeführt werden. Im 2. Fall kann man hingegen direkt auf die Indizes setzen.

[haentschman]

Hallöle...:P

Zitat:

Im 2. Fall kann man hingegen direkt auf die Indizes setzen.

Ok...aber das das soviel ausmacht? :gruebel: Again what learned...:stupid:

[Delphi.Narium]

Was passiert denn bei

SQL-Code:

UPDATE
  ADR
SET
  AdrValue = select SUM(B.BelValue) as Summe from BEL B where IIF(B.Adr='', B.KDNR, B.Adr) = ADR.Adr

[haentschman]

Danke...:P
Ich muß dich aber vertrösten... Ich melde mich wenn ich das probiert habe. :wink:

[haentschman]

Update 1:

Zitat:

IIF(B.Adr='', B.KDNR, B.Adr)

Zitat:

Meldung 195, Ebene 15, Status 10, Zeile 11
IFF wird nicht als Name einer integrierten Funktion erkannt.

:gruebel::cry:

Update 2:

Code:

UPDATE
  ADR
SET
  AdrValue = (select SUM(B.BelValue) as Summe from BEL B
                where B.Adr = ADR.Adr)
              +
             (select SUM(B.BelValue) as Summe from BEL B
                where B.KDNR = ADR.Adr)

...geht plötzlich nicht mehr. :shock: Beide Subselects liefern Werte, aber das Update(AdrValue) ist NULL...:twisted:

[jobo]

Also wenn eine der Summen NULL ist, weil keine Datensätze zum Summieren da sind, dann ist auch die Summe der Addition NULL, oder?
Also muss noch ein Coalesce, NVL, .. rein, weiß nicht was fb da genau mag.

P.S:
"Beide Select liefern Werte" müsste dazu allerdings im Einzelfall nicht erfüllt sein. (Während es generell, also ohne Datensatzbeschränkung, Werte liefert)

[haentschman]

Danke...:P

Code:

UPDATE
  ADR
SET
  AdrValue = (select SUM(B.BelValue) as Summe from BEL B
                where B.Adr = ADR.Adr)

...macht ein Update mit einem Wert von 10

Code:

UPDATE
  ADR
SET
  AdrValue = (select SUM(B.BelValue) as Summe from BEL B
                where B.KDNR = ADR.Adr)

...macht ein Update mit einem Wert von 5


aber zusammen: (select) + (select) ...macht ein Update mit einem Wert von NULL

Das ist doch nur Addition...oder? :evil:

PS: Das Statement mit + hat vorher funktioniert...auf den Cent genau. Nur jetzt nicht mehr. :?

Nachtrag:

Zitat:

Also wenn eine der Summen NULL ist, weil keine Datensätze zum Summieren da sind, dann ist auch die Summe der Addition NULL, oder?

...prüfe ich mal.

[Delphi.Narium]

Zitat:

Zitat von haentschman (Beitrag 1467531)

Update 1:

Zitat:

IIF(B.Adr='', B.KDNR, B.Adr)

Zitat:

Meldung 195, Ebene 15, Status 10, Zeile 11
IFF wird nicht als Name einer integrierten Funktion erkannt.

:gruebel::cry:

Da hat er recht, es heißt auch nicht IFF sondern IIF
also nicht Iiii Äfff Äfff sondern Iiii Iiii Äfff ;-)

[Ghostwalker]

Oder..wie siehts damit aus:


UPDATE
ADR
SET
AdrValue = (select SUM(B.BelValue) as Summe
from BEL B
where IIF(Trim(B.Adr) is NULL, B.KDNR, B.Adr) = ADR.Adr

[haentschman]

Zitat:

also nicht Iiii Äfff Äfff sondern Iiii Iiii Äfff

:oops: ...urlaubsreif. Ich melde mich...:P

Nachtrag:
Die Geschwindigkeit liegt eher schlecht wie das mit dem case...:? (mit meiner Datemenge 38s)

[Uwe Raabe]

Zitat:

Zitat von haentschman (Beitrag 1467612)

Die Geschwindigkeit liegt eher schlecht wie das mit dem case...:? (mit meiner Datemenge 38s)

Das liegt vielleicht daran, dass der Ausdruck für jeden Record ausgewertet werden muss. Ein Index auf B.KDNR und/oder B.Adr hat somit keine Wirkung. Das läuft dann vermutlich auf einen Full Scan hinaus.

[haentschman]

Zitat:

Das läuft dann vermutlich auf einen Full Scan hinaus.

...eben. :P

[jobo]

Ja, Fullscan ist ein gutes Stichwort.

Mit Deinen Mengenangaben habe ich mal probiert.
8000 Adressen und ca 2 Mio dazu passende Belege ergibt ca 15 Sekunden für ein Update mit Fullscans. Das ist aber unter Postgres und ohne Indizierung (wg Fullscan eh egal). (Wenn es keine Varchar Felder sind sondern Integer, ist es deutlich schneller, hatte ich erst nicht drauf geachtet)
Ob es nun mit >,<,= '' oder anders ist, macht plus/minus 1 Sekunde. Die Exaktheit der Summen ist ein Thema für sich, denke ich. Die DB ist natürlich nicht 3GB groß, da sie nur diese Daten enthält. Auch das ist dann aber bei einem Fullscan wahrscheinlich ein Thema.
Da es generierte Daten sind, sind sie kontinuierlich und natürlich passend generiert. In der Realität sicher anders und langsamer. Dafür auf einem alten Laptop..

Sind das realistische Werte? Dann kann man ja mal auf Firebird umsteigen.

Was die Richtigkeit der Summen angeht: Hast Du mal erhoben, welche ID wie belegt sind? Werden ja nach Ausprägung des Update vielleicht Werte doppelt summiert?
select kdnr, adr from belege where kdnr = adr;
select kdnr, adr from belege where kdnr = '' and adr ='';
select kdnr, adr from belege where kdnr != adr and adr is not null and adr !='';
usw.