SQL 2000 View erweitern, Hilfe benötigt.

[Brudertac]

Hallo zusammen,

ich habe hier folgenden View der in dieser Variante gut Funktioniert:


SELECT dbo.BelegKopfHandwerk.Belegnummer, dbo.BelegKopfHandwerk.Belegart, dbo.BelegKopfHandwerk.Kundennummer, dbo.BelegKopfHandwerk.Betreff,
dbo.BelegKopfHandwerk.Belegdatum, dbo.BelegKopfHandwerk.Status, dbo.objektdataBeleg1.text2, dbo.objektdataBeleg1.Belegnummer AS BL2,
dbo.objektdataBeleg1.gesperrtvonuser, dbo.BelegPositionHandwerk.Monteur, dbo.Personalstamm.Nachname, dbo.BelegStatus.StatusBezeichnung,
dbo.objektdataBeleg1.Nachverfolgung
FROM dbo.BelegKopfHandwerk LEFT OUTER JOIN
dbo.objektdataBeleg1 ON dbo.BelegKopfHandwerk.Belegnummer = dbo.objektdataBeleg1.Belegnummer LEFT OUTER JOIN
dbo.BelegPositionHandwerk ON dbo.BelegKopfHandwerk.Belegnummer = dbo.BelegPositionHandwerk.Belegnummer AND
dbo.BelegPositionHandwerk.Positionstyp = 10 LEFT OUTER JOIN
dbo.Personalstamm ON dbo.BelegPositionHandwerk.Monteur = dbo.Personalstamm.PersonalNr LEFT OUTER JOIN
dbo.BelegStatus ON dbo.BelegKopfHandwerk.Status = dbo.BelegStatus.StatusNummer


Das Ergebnis sieht (verkürzt) so aus:


Belegnummer, Belegart, Betreff
123456789, 100, Betrefftext


Nun soll das Ergebniss des View um den Inhalt einer weiteren Tabelle erweitert werden.
Allerdings will ich nur den Inhalt einer bestimmten Spalte haben und muss diesen außerdem auf
mehrere neue Ergebnissfelder aufteilen.

Im Test klappt dies mit folgendem Code ganz gut:


SELECT SUM(CASE WHEN FileKategorie = 'heizung' THEN 1 ELSE 0 END) AS TypeHeizung, SUM(CASE WHEN FileKategorie = 'sanitaer' THEN 1 ELSE 0 END)
AS TypeSanitaer, SUM(CASE WHEN FileKategorie = 'architektur' THEN 1 ELSE 0 END) AS TypeArchitektur,
SUM(CASE WHEN FileKategorie = 'sonstiges' THEN 1 ELSE 0 END) AS TypeSonstiges,
SUM(CASE WHEN FileKategorie = 'anleitungen' THEN 1 ELSE 0 END) AS TypeAnleitungen,
SUM(CASE WHEN FileKategorie = 'archiv' THEN 1 ELSE 0 END) AS TypeArchiv,
SUM(CASE WHEN FileKategorie = 'baustellenbilder' THEN 1 ELSE 0 END) AS TypeBaustellenbilder,
SUM(CASE WHEN FileKategorie = '3dansicht' THEN 1 ELSE 0 END) AS Type3dansicht
FROM objektdataDateisystem
WHERE (Belegnummer = 123456789)


Das Ergebnis sollte (verkürzt) so aussehen:


Belegnummer, Belegart, Betreff, TypeHeizung, TypeSanitaer, TypeSonstiges
123456789, 100, Betrefftext, 4, 0, 9



Nun hab ich allerdings überhaupt keine Idee wie ich das in den bestehenden View bekomme.

Habt Ihr hier vielleicht einen Tipp für mich?
Besten Dank!

[Matrix316]

Die erste Abfrage erweitern mit

LEFT OUTER JOIN dbo.objektdataDateisystem (oder inner join, je nach dem)
ON dbo.objektdataDateisystem.Belegnummer = dbo.objektdataBeleg1.Belegnummer

und dann hinter Nachverfolgung ein ,

und dann

SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'heizung' THEN 1 ELSE 0 END) AS TypeHeizung, SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'sanitaer' THEN 1 ELSE 0 END)
AS TypeSanitaer, SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'architektur' THEN 1 ELSE 0 END) AS TypeArchitektur,
SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'sonstiges' THEN 1 ELSE 0 END) AS TypeSonstiges,
SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'anleitungen' THEN 1 ELSE 0 END) AS TypeAnleitungen,
SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'archiv' THEN 1 ELSE 0 END) AS TypeArchiv,
SUM(CASE WHEN objektdataDateisystem.FileKategorie = 'baustellenbilder' THEN 1 ELSE 0 END) AS TypeBaustellenbilder,
SUM(CASE WHEN objektdataDateisystem.FileKategorie = '3dansicht' THEN 1 ELSE 0 END) AS Type3dansicht

oder so.

Wobei es einfacher ist beim Join die Tabelle mit einem alias zu versehen, damit man nicht bei jeder Spalte den ganzen Tabellenname im select Teil reinschreiben muss.

[Brudertac]

Danke für deine Hilfe, ich hab das jetzt mal so erweitert aber ich erhalte eine Fehlermeldung.

Der Code schaut jetzt so aus:


SELECT dbo.BelegKopfHandwerk.Belegnummer, dbo.BelegKopfHandwerk.Belegart, dbo.BelegKopfHandwerk.Kundennummer,
dbo.BelegKopfHandwerk.Betreff, dbo.BelegKopfHandwerk.Belegdatum, dbo.BelegKopfHandwerk.Status, dbo.objektdataBeleg1.text2,
dbo.objektdataBeleg1.Belegnummer AS BL2, dbo.objektdataBeleg1.gesperrtvonuser, dbo.BelegPositionHandwerk.Monteur,
dbo.Personalstamm.Nachname, dbo.BelegStatus.StatusBezeichnung, dbo.objektdataBeleg1.Nachverfolgung,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'heizung' THEN 1 ELSE 0 END) AS TypeHeizung,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'sanitaer' THEN 1 ELSE 0 END) AS TypeSanitaer,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'architektur' THEN 1 ELSE 0 END) AS TypeArchitektur,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'sonstiges' THEN 1 ELSE 0 END) AS TypeSonstiges,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'anleitungen' THEN 1 ELSE 0 END) AS TypeAnleitungen,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'archiv' THEN 1 ELSE 0 END) AS TypeArchiv,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = 'baustellenbilder' THEN 1 ELSE 0 END) AS TypeBaustellenbilder,
SUM(CASE WHEN dbo.objektdataDateisystem.FileKategorie = '3dansicht' THEN 1 ELSE 0 END) AS Type3dansicht
FROM dbo.BelegKopfHandwerk LEFT OUTER JOIN
dbo.objektdataBeleg1 ON dbo.BelegKopfHandwerk.Belegnummer = dbo.objektdataBeleg1.Belegnummer LEFT OUTER JOIN
dbo.BelegPositionHandwerk ON dbo.BelegKopfHandwerk.Belegnummer = dbo.BelegPositionHandwerk.Belegnummer AND
dbo.BelegPositionHandwerk.Positionstyp = 10 LEFT OUTER JOIN
dbo.Personalstamm ON dbo.BelegPositionHandwerk.Monteur = dbo.Personalstamm.PersonalNr LEFT OUTER JOIN
dbo.BelegStatus ON dbo.BelegKopfHandwerk.Status = dbo.BelegStatus.StatusNummer LEFT OUTER JOIN
dbo.objektdataDateisystem ON dbo.BelegKopfHandwerk.Belegnummer = dbo.objektdataDateisystem.Belegnummer


Jetzt erhalte ich aber folgende Fehler:


Die dbo.BelegKopfHandwerk.Belegnummer-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegKopfHandwerk.Belegart-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegKopfHandwerk.Kundennummer-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegKopfHandwerk.Betreff-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegKopfHandwerk.Belegdatum-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegKopfHandwerk.Status-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.objektdataBeleg1.text2-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.objektdataBeleg1.Belegnummer-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.objektdataBeleg1.gesperrtvonuser-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegPositionHandwerk.Monteur-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.Personalstamm.Nachname-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.BelegStatus.StatusBezeichnung-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.
Die dbo.objektdataBeleg1.Nachverfolgung-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist.




EDIT: Hab jetzt alle in ein "GROUP BY" aufgenommen und jetzt gehts. Das muss so sein oder sollte man das anders lösen?

[samm]

Das muss so sein. Wenn du eine Aggregatsfunktion wie SUM benutzt, dann müssen die Spalten, die im SELECT ausserhalb der Aggregatsfunktion vorkommen, gruppiert sein, weil sonst nicht klar ist, was das Ergebnis sein soll.

Beispiel:
Zahl
----
3
4

SELECT Sum(Zahl) FROM Tabelle
--> 7

Wort | Zahl
-------|-----
"bla" | 3
"blah" | 5
"bla" | 4
"bla" | 2

SELECT Wort, SUM(Zahl) FROM Tabelle
--> sowas wie
"bla" | 14
"blah" | 14
"bla" | 14
"bla" | 14 ?

oder vielleicht
"bla" | 3
"blah" | 5
"bla" | 6 ?

Deswegen brauchst du
SELECT Wort, Sum(Zahl) FROM Tabelle GROUP BY Wort
-->
"bla" | 9
"blah" | 5


Was das Anders-Lösen angeht: Ich finde die Konstruktion mit SUM(CASE Stringvergleich) sehr hässlich. Wahrscheinlich wäre es nicht praktikabel, das Schema anzupassen, aber wenn die Kategorien per numerischem Fremdschlüssel auf eine Kategorien-Tabelle angegeben wären, würde mir das schon etwas besser gefallen ;) Ausserdem könntest wohl per COUNT() und GROUP BY FileKategorie sowas evt. wesentlich schneller erreichen (wenn Geschwindigkeit eine Rolle spielt), und wärst ausserdem flexibler. Um dein Beispiel zu verwenden:
Belegnummer, Belegart, Betreff, FileKategorie, Anzahl
123456789, 100, Betrefftext, 'heizung', 4
123456789, 100, Betrefftext, 'sanitaer', 0
123456789, 100, Betrefftext, 'sonstiges', 9
Damit wärst du auch für andere Werte von FileKategorie gewappnet, ohne die View anpassen zu müssen. Aber klar, je nach Verwendungszweck ist es halt wichtig, nur eine Zeile zurückzubekommen.

[Matrix316]

Ist so. Warum, kann ich dir auch nicht genau sagen. ;) Und wer das hier versteht, bekommt einen Keks: http://www.sql-und-xml.de/sql-tutorial/group-by-zum-aggregieren-auswerten-von-daten.html ;)

Ok, jetzt hab ichs auch kapiert. ;)

Ich frage mich nur, warum es einen Fehler gibt. Man könnte ja standardmäßig alles summieren und je nach Bedarf gruppieren. =)

[samm]

[STRIKE]Man könnte ja standardmäßig alles summieren und je nach Bedarf gruppieren. =)Formuliere "alles summieren und je nach Bedarf" mal als saubere Definition ;) Das Problem ist, dass die Zeilen *eigentlich* ohne Reihenfolge gespeichert sind, weil es konzeptionell um eine Menge von Tupeln handelt. Mein Beispiel von
Wort | Zahl
-------|-----
"bla" | 3
"blah" | 5
"bla" | 4
"bla" | 2
Kann genauso gut so aussehen:

Wort | Zahl
-------|-----
"bla" | 4
"bla" | 3
"blah" | 5
"bla" | 2
oder gar verteilt gespeichert sein etc. Wenn also das "je nach Bedarf" heissen würde, "wenn gleiche Wort-Werte aufeinander folgen", dann wäre das Ergebnis einmal
"bla" 3
"blah" 5
"bla" 6
und das zweite Mal
"bla" 7
"blah" 5
"bla" 2

[Edit]
Wenn das "standardmässig alles summieren" gefragt ist, dann mach halt
SELECT t1.Wort, t2.Summe
FROM Tabelle AS t1, (SELECT SUM(Zahl) AS Summe FROM Tabelle) AS t2

-->
"bla" 14
"blah" 14
"bla" 14
"bla" 14

[Matrix316]

Ja, das letzte Beispiel mein ich mit "alles summieren".

Und bei Bedarf dann Group by optional.

Die Fehlermeldung verwirrt halt ziemlich, weil man erstmal wissen muss: "Was ist eine Aggregatfunktion?" ;)

[Gast]

Wenn also das "je nach Bedarf" heissen würde, "wenn gleiche Wort-Werte aufeinander folgen", dann wäre das Ergebnis einmal
"bla" 3
"blah" 5
"bla" 6
und das zweite Mal
[code]"bla" 7
"blah" 5
"

Wie kommst du darauf? So richtig verstehe ich deinen Gedankengang nicht. Die Aggregierung erfolgt ja auf der Gruppierung. Und die Gruppierung erfolgt erst nach dem SELECT.

Ich habe Matrix so verstanden:

Wenn man jetzt folgende Querry hat:

SELECT Spalte1, Spalte2, Spalte3, SUM(Spalte4) FROM Tabelle
GROUP BY Spalte1, Spalte2

Dann sollte es zumindest bei der SUM Funktion möglich sein, dass Spalte4 über die Gruppe (Spalte1, Spalte2) summiert wird. Unabhängig davon, ob Spalte3 zur selben Gruppe gleiche oder unterschiedliche Werte hat.

Das ganze ist eben wahrscheinlich einfach nicht im SQL Standard definiert oder man geht im Standard davon aus, dass die SELECT Zeile auch der Gruppe entspricht, was bei der Ausgabe vielleicht weniger Verwirrung erzeugt.

[samm]

Wie kommst du darauf? So richtig verstehe ich deinen Gedankengang nicht. Ich bin ja auch nur am Raten, was Matrix gemeint haben könnte ;)

Das ganze ist eben wahrscheinlich einfach nicht im SQL Standard definiert oder man geht im Standard davon aus, dass die SELECT Zeile auch der Gruppe entspricht, was bei der Ausgabe vielleicht weniger Verwirrung erzeugt.Es ist nicht definiert, weil: wie sollte es denn definiert sein? Dein Beispiel mal mit Werten:

Spalte1, Spalte2, Spalte3, Spalte4
"bla", "bla", "bli", 1
"bla", "bla", "blu", 2

SELECT Spalte1, Spalte2, Spalte3, SUM(Spalte4) FROM Tabelle
GROUP BY Spalte1, Spalte2

Soll was bitte ergeben? "bla", "bla", "???", 3?

[Gast]

Ich bin ja auch nur am Raten, was Matrix gemeint haben könnte ;)

Es ist nicht definiert, weil: wie sollte es denn definiert sein? Dein Beispiel mal mit Werten:

Spalte1, Spalte2, Spalte3, Spalte4
"bla", "bla", "bli", 1
"bla", "bla", "blu", 2

SELECT Spalte1, Spalte2, Spalte3, SUM(Spalte4) FROM Tabelle
GROUP BY Spalte1, Spalte2

Soll was bitte ergeben? "bla", "bla", "???", 3?

Kommt eben darauf an, wie man es definiert. Man könnte z.B. folgendes Ergebnis haben, wenn man die Gruppierung vom SELECT entkoppeln würde.

bla, bla, bli, 3
bla, bla, blu, 3

Ob das Sinnvoll ist, steht auf einem anderen Blatt bzw. will ich darüber gar nicht diskutieren.

[Brudertac]

Das muss so sein. Wenn du eine Aggregatsfunktion wie SUM benutzt, dann müssen die Spalten, die im SELECT ausserhalb der Aggregatsfunktion vorkommen, gruppiert sein, weil sonst nicht klar ist, was das Ergebnis sein soll.

Was das Anders-Lösen angeht: Ich finde die Konstruktion mit SUM(CASE Stringvergleich) sehr hässlich. Wahrscheinlich wäre es nicht praktikabel, das Schema anzupassen, aber wenn die Kategorien per numerischem Fremdschlüssel auf eine Kategorien-Tabelle angegeben wären, würde mir das schon etwas besser gefallen ;) Ausserdem könntest wohl per COUNT() und GROUP BY FileKategorie sowas evt. wesentlich schneller erreichen (wenn Geschwindigkeit eine Rolle spielt), und wärst ausserdem flexibler. Um dein Beispiel zu verwenden:
Belegnummer, Belegart, Betreff, FileKategorie, Anzahl
123456789, 100, Betrefftext, 'heizung', 4
123456789, 100, Betrefftext, 'sanitaer', 0
123456789, 100, Betrefftext, 'sonstiges', 9
Damit wärst du auch für andere Werte von FileKategorie gewappnet, ohne die View anpassen zu müssen. Aber klar, je nach Verwendungszweck ist es halt wichtig, nur eine Zeile zurückzubekommen.

Das Schema anzupassen will ich mir nicht wirklich antun. Es funktioniert so jetzt auch ganz gut. :smile:
Das Thema Kategorie-Tabelle... Ich hab das damals bei allem möglichen gemacht und hier irgendwie nicht.
Jetzt alles anzupassen muss nicht sein, zumindest im Moment nicht. :rolleyes:

Besten Dank nochmal an alle Helfer!!!!

[interzone]

ein kleiner Tipp..."vernünftige" Tabellenaliase machen SQL-Code wesentlich lesbarer.
Solche Statements gehen ja mal gar nicht.