Archiv verlassen und diese Seite im Standarddesign anzeigen : Oracle / SQL CASE wird übersehen?
SELECT SUM(
(CASE f.dienstrangID
WHEN 100 THEN g.monatsgehalt*15
WHEN 110 THEN g.monatsgehalt*15
WHEN 105 THEN g.monatsgehalt*15
ELSE g.monatsgehalt*14
END)),
SUM(
(CASE b.abteilungsID
WHEN 161 THEN g.monatsgehalt*12
ELSE g.monatsgehalt*14
END))
AS gehalt
FROM gehaltsstufe g
INNER JOIN personal p
ON g.gehaltsstufeID = p.gehaltsstufeID
INNER JOIN flugpersonal f
ON p.personID = f.personID
Left JOIN bodenpersonal b
ON p.personID = b.personID
ich hab diesen code ...
während das erste case richtig abgearbeitet wird habe ich das problem das das 2. case nicht korekt abgearbeitet wird...
also: in der tabelle bodenpersonal gibt es 2 leute mit abteilungsID 161... allerdings verändert sich das ausgerechnete jahresgehalt nicht wenn ich beim 2. case den multiplikator ändere
ändere ich ihn jedoch beim 2. case bei else ändert sich das gehalt korrekt
hat jemand nen tip für mich?
danke
abteilungsID klingt so, als sollte es noch eine Abteilungs-Tabelle geben, die mit in den join gehört?
Falls die Abteilung nicht "erwischt" wird, landet er dann immer im "else" Zweig.
(Kenne natürlich die genaue Tabellenstruktur nicht)
Gruß!
nefu
Edit : oder fragst Du bewusst die AbteilungsID (nicht die Abteilung 161) ab?
so hier ist die tabellenstruktur
http://ntbab.dyndns.org/apache2-default/seite/download/bilder/schule/flughafen.jpg
und ja ich frage bewust die spalte AbteilungsID ab
Damit ich das richtig verstehe:
Du willst also zwei (hochgerechnete) Gehaltssummen
- einmal für das Flugpersonal
- einmal für das Bodenpersonal
wobei der Hochrechnungsfaktor beim Flugpersonal vom Dienstrang abhängt, beim Bodenpersonal von der Abteilung (bzw. von den jeweiligen IDs).
Nur das Flugpersonal hat einen Dienstrang.
Nur das Bodenpersonal gehört zu einer Abteilung.
Richtig?
nefu
Genau 2 verschiedene Gehaltssummen.. allerdings soll ich beide in einer einzigen sql query herausbekommen....
du hast es genau erkannt
gr@fz@hL
2007-10-15, 22:12:15
Lass dir mal "select * from <joingedöns> ausgeben und schau dir mal an, welche abteilungsids überhaupt vertreten sind, vielleicht kommt die 161 gar nicht vor.
hm wenn ich select * from joins mache liefert er mir alles passend zurück bis darauf das die abteilungsIDs des bodenpersonals nicht ausgelsen... anscheinend hats was mit meinem join
irgendwie stehe ich gerade auf dem Schlauch.
Tabellen anlegen :
scott@TEST102 >create table personal(personid number, gehaltsstufeid number);
Tabelle wurde erstellt.
scott@TEST102 >create table gehaltsstufe(gehaltsstufeid number, gehalt number(7,2));
Tabelle wurde erstellt.
scott@TEST102 >create table flugpersonal(personid number, dienstrangid number);
Tabelle wurde erstellt.
scott@TEST102 >create table bodenpersonal(personid number, abteilungsid number);
Tabelle wurde erstellt.
Gehaltstabelle füllen :
scott@TEST102 >insert into gehaltsstufe values(1, 1000);
1 Zeile wurde erstellt.
scott@TEST102 >insert into gehaltsstufe values(2, 2000);
1 Zeile wurde erstellt.
scott@TEST102 >insert into gehaltsstufe values(3, 3000);
1 Zeile wurde erstellt.
scott@TEST102 >commit;
Transaktion mit COMMIT abgeschlossen.
personal tabelle füllen:
scott@TEST102 >insert into personal values(1, 1);
1 Zeile wurde erstellt.
scott@TEST102 >insert into personal values(2, 1);
1 Zeile wurde erstellt.
scott@TEST102 >insert into personal values(3, 2);
1 Zeile wurde erstellt.
scott@TEST102 >insert into personal values(4, 2);
1 Zeile wurde erstellt.
scott@TEST102 >insert into personal values(5, 3);
1 Zeile wurde erstellt.
scott@TEST102 >insert into personal values(6, 3);
1 Zeile wurde erstellt.
scott@TEST102 >commit;
Transaktion mit COMMIT abgeschlossen.
Ergibt folgendes Zwischenergebnis:
scott@TEST102 >select personid, gehalt
2 from personal p, gehaltsstufe g
3 where p.gehaltsstufeid = g.gehaltsstufeid
4 order by personid;
PERSONID GEHALT
---------- ----------
1 1000
2 1000
3 2000
4 2000
5 3000
6 3000
6 Zeilen ausgewählt.
Je 3 Personen dem Boden- und dem Flugpersonal zuordnen:
scott@TEST102 >-- personid 1 zum bodenpersonal mitabteilungsid 160
scott@TEST102 >insert into bodenpersonal values(1, 160);
1 Zeile wurde erstellt.
scott@TEST102 >-- personid 3 zum bodenpersonal mitabteilungsid 161
scott@TEST102 >insert into bodenpersonal values(3, 161);
1 Zeile wurde erstellt.
scott@TEST102 >-- personid 5 zum bodenpersonal mitabteilungsid 162
scott@TEST102 >insert into bodenpersonal values(5, 162);
1 Zeile wurde erstellt.
scott@TEST102 >commit;
Transaktion mit COMMIT abgeschlossen.
scott@TEST102 >-- personid 2 zum flugpersonal, dienstrangid 100
scott@TEST102 >insert into flugpersonal values(2, 100);
1 Zeile wurde erstellt.
scott@TEST102 >-- personid 4 zum flugpersonal, dienstrangid 110
scott@TEST102 >insert into flugpersonal values(4, 110);
1 Zeile wurde erstellt.
scott@TEST102 >-- personid 6 zum flugpersonal, dienstrangid 120
scott@TEST102 >insert into flugpersonal values(6, 120);
1 Zeile wurde erstellt.
scott@TEST102 >commit;
Summen getrennt errechnen :
scott@TEST102 >-- summe flugpersonal manuell hochgerechnet
scott@TEST102 >select (15 * 1000) + (15 * 2000) + (14 * 3000) from dual;
(15*1000)+(15*2000)+(14*3000)
-----------------------------
87000
scott@TEST102 >-- als sql query
scott@TEST102 >SELECT SUM(
2 (CASE f.dienstrangID
3 WHEN 100 THEN g.gehalt*15
4 WHEN 110 THEN g.gehalt*15
5 WHEN 105 THEN g.gehalt*15
6 ELSE g.gehalt*14
7 END)) SUMMEFLUGP
8 FROM personal p, gehaltsstufe g, flugpersonal f
9 WHERE p.personid = f.personid and
10 p.gehaltsstufeid = g.gehaltsstufeid;
SUMMEFLUGP
----------
87000
scott@TEST102 >
scott@TEST102 >-- summe bodenpersonal manuell hochgerechnet
scott@TEST102 >select (14 * 1000) + (12 * 2000) + (14 * 3000) from dual;
(14*1000)+(12*2000)+(14*3000)
-----------------------------
80000
scott@TEST102 >-- sql query
scott@TEST102 >SELECT SUM(
2 (CASE b.abteilungsID
3 WHEN 161 THEN g.gehalt*12
4 ELSE g.gehalt*14
5 END)) SUMMEBODENP
6 FROM personal p, gehaltsstufe g, bodenpersonal b
7 WHERE p.personid = b.personid and
8 p.gehaltsstufeid = g.gehaltsstufeid;
SUMMEBODENP
-----------
80000
scott@TEST102 >
Der folgende Befehl gibt mir alle Personen mit dem Gehalt aus, ggf. mit Abteilung- bzw. DienstrangID:
scott@TEST102 > SELECT gehalt gehalt, abteilungsid abteilungsid, NULL dienstrangid
2 FROM personal p, gehaltsstufe g, bodenpersonal b
3 WHERE p.personid = b.personid and
4 p.gehaltsstufeid = g.gehaltsstufeid
5 UNION
6 SELECT gehalt gehalt, NULL abteilungsid, dienstrangid dienstrangid
7 FROM personal p, gehaltsstufe g, flugpersonal f
8 WHERE p.personid = f.personid and
9 p.gehaltsstufeid = g.gehaltsstufeid;
GEHALT ABTEILUNGSID DIENSTRANGID
---------- ------------ ------------
1000 160
1000 100
2000 161
2000 110
3000 162
3000 120
6 Zeilen ausgewählt.
Aus dieser Menge sollten sich doch die korrekten Summen ermitteln lassen - aber irgendwie geht es immer schief :confused:
Bin wohl schon zu müde :redface:
Nefu
Hmm..
kaum nimmt man 0 statt NULL (und qualmt schnell mal eine auf dem Balkon :biggrin:), schon funktioniert es :
scott@TEST102 >SELECT SUM(
2 (CASE abteilungsID
3 WHEN 0 THEN gehalt * 0
4 WHEN 161 THEN gehalt*12
5 ELSE gehalt*14
6 END)) SUMMEBODENP,
7 SUM(
8 (CASE dienstrangID
9 WHEN 0 THEN gehalt * 0
10 WHEN 100 THEN gehalt*15
11 WHEN 110 THEN gehalt*15
12 WHEN 105 THEN gehalt*15
13 ELSE gehalt*14
14 END)) SUMMEFLUGP
15 FROM(
16 SELECT gehalt gehalt, abteilungsid abteilungsid, 0 dienstrangid
17 FROM personal p, gehaltsstufe g, bodenpersonal b
18 WHERE p.personid = b.personid and
19 p.gehaltsstufeid = g.gehaltsstufeid
20 UNION
21 SELECT gehalt gehalt, 0 abteilungsid, dienstrangid dienstrangid
22 FROM personal p, gehaltsstufe g, flugpersonal f
23 WHERE p.personid = f.personid and
24 p.gehaltsstufeid = g.gehaltsstufeid);
SUMMEBODENP SUMMEFLUGP
----------- ----------
80000 87000
scott@TEST102 >
Muss wohl dringend mal wieder meine SQL Kenntnisse auffrischen. ;(
Wie das in die von Dir bevorzugte INNER- / OUTER- /LEFT JOIN Syntax zu übersetzen ist musst Du selber herausfinden.
Als ich SQL gelernt habe (lang ist es her ...) gab es das noch nicht :D)
Gruß!
nefu
Edit: Falls Du das so übernehmen willst (das geht bestimmt auch eleganter) solltest Du prüfen, ob Du nicht UNION ALL verwenden solltest.
Sonst gehen Dir eventull "identische Sätze" verloren.
Einfach in der Oracle Doku mal nachlesen - bin jetzt selber zu müde.
Gute Nacht!
Gute Nacht
also erstmal ein ganz großes WOW und DANKE ich schau mir die lösung mal an und denk sie mir durch!
werd wahrscheinlich noch öfter nervern gg pl/sql kannst du nicht vielleicht ^^
SELECT SUM(
(CASE dienstrangID
WHEN 100 THEN monatsgehalt*15
WHEN 110 THEN monatsgehalt*15
WHEN 105 THEN monatsgehalt*15
WHEN 161 THEN monatsgehalt*12
ELSE monatsgehalt*14
END))
FROM
(SELECT * from gehaltsstufe g
INNER JOIN personal p
ON g.gehaltsstufeID = p.gehaltsstufeID
INNER JOIN flugpersonal f
ON p.personID = f.personID
UNION
SELECT * FROM gehaltsstufe g
INNER JOIN personal p
ON g.gehaltsstufeID = p.gehaltsstufeID
INNER JOIN bodenpersonal b
ON p.personID = b.personID)
meine denke ich jetzt funktionierende lösung ... nur wenn interesse besteht
Keine Ursache!
Ich bin schon einige Zeit kaum noch zum (PL/)SQL Programmieren gekommen und fand es mal wieder ganz reizvoll.
Allerdings erschreckend, wie schnell man vergisst!
Das Problem ist ja, dass das Bodenpersonal über die AbteilungsID gejoint werden muss, und das Flugpersonal über den Dienstrang.
Naja, jetzt ist es erstmal Zeit zum Schlafen!
Den Hinweis zu UNION / UNION ALL hast Du gesehen?
Gruß!
Nefu
ich habe ja jetzt oben das ebenfalls mit union gelöst... zu dumm dass ich nicht selbst draufgekommen bin!
jetzt habe ich aber eine abschließende frage und zwar soweit ich weiß gibt UNION alle werten aus die in der 1. Tabelle und/oder in der 2. Tabelle stehen
wenn ich jetzt das eingebe
SELECT *
FROM
(SELECT * from gehaltsstufe g
INNER JOIN personal p
ON g.gehaltsstufeID = p.gehaltsstufeID
INNER JOIN flugpersonal f
ON p.personID = f.personID
UNION
SELECT * FROM gehaltsstufe g
INNER JOIN personal p
ON g.gehaltsstufeID = p.gehaltsstufeID
INNER JOIN bodenpersonal b
ON p.personID = b.personID)
habe ich damit gerechnet eine spalte abteilungsID und eine spalte dienstrangID zu erhalten ... allerding mergt er mir alles in eine Spalte names dienstragID (also die relevanten IDs für die gehaltsberechnung) zusammen?!
DIENSTRANGID
121
161
161
210
210
1
100
105
btw. gute nacht! : )
Hi,
UNION alleine zeigt nur die 'distinct values' an, 'UNION ALL' gibt alle Sätze zurück. Bei meiner Fallkonstellation funktioniert natürlich beides, da eine Kombination von Gehaltsgruppe und Abteilungs- bzw. DienstrangID nicht mehrfach vorkommt.
Das Ganze ist von mir gestern ziemlich 'quick and dirty' gemacht worden, so habe ich z.B. keine constraints definiert, weil das für die Lösung keine Rolle spielt.
Die Abfrage auf IDs (surrogate keys) ist auch 'unschön'. Wenn das Schema mal geleert und neu geladen wird, gibt es in der Regel auch neue IDs, während die eigentlichen Werte gleich bleiben. (Die IDs werden ja üblicherweise über sequences befüllt, die einfach weiter aufsteigende Werte vergeben).Allerdings gibt es in Deinem Schema auch keine Abteilungsnummer, sondern wohl nur eine Kurzbezeichnung und einen Langtext.
Bei SQL operierst Du ja üblicherweise immer mit Ergebnismengen. Wenn Du zwei Mengen über UNION (ALL) vereinigst, sollten beide den gleichen Aufbau haben, was die Attribute angeht. Da das Bodenpersonal keinen Dienstrang hat, das Flugpersonal aber zu keiner Abteilung gehört, habe ich die Werte im jeweiligen JOIN auf einen konstanten Wert gesetzt (0). Setzt natürlich voraus, dass 0 keine gültige (verwendete ID) für Dienstrang oder Abteilung ist. Notfalls gehen ja noch negative Werte (-1).
Im jeweiligen CASE Statement werden nun die nicht zum Boden- bzw. Flugpersonal gehörenden Sätze ausgefiltert (WHEN 0 ...), und nur für die relevanten Fälle das Gehalt hochgerechnet.
Deine Abfrage sieht aber nur ein Attribut vor, welches - je nachdem - entweder AbteilungsID oder DienstrangID ist. Vergleich es mal mit dem von mir aufgeführten Statement, das nur über UNION (besser UNION ALL) die beiden SELECTs kombiniert und die 6 Ergebnissätze ausspuckt (allerdings noch mit NULL statt 0 als konstantem Wert).
Einfach mal ein bisschen rumspielen und ausprobieren. :wink:
Viel Spass noch!
nefu
meine denke ich jetzt funktionierende lösung ...
Das gibt aber die Gesamtsumme von Boden- plus Flugpersonal aus?!
Statement auf meine Tabellen angepasst:
Verbunden mit:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
scott@TEST102 >SELECT SUM(
2 (CASE dienstrangID
3 WHEN 100 THEN gehalt*15
4 WHEN 110 THEN gehalt*15
5 WHEN 105 THEN gehalt*15
6 WHEN 161 THEN gehalt*12
7 ELSE gehalt*14
8 END))
9 FROM
10 (SELECT * from gehaltsstufe g
11 INNER JOIN personal p
12 ON g.gehaltsstufeID = p.gehaltsstufeID
13 INNER JOIN flugpersonal f
14 ON p.personID = f.personID
15 UNION
16 SELECT * FROM gehaltsstufe g
17 INNER JOIN personal p
18 ON g.gehaltsstufeID = p.gehaltsstufeID
19 INNER JOIN bodenpersonal b
20 ON p.personID = b.personID);
SUM((CASEDIENSTRANGIDWHEN100THENGEHALT*15WHEN110THENGEHALT*15WHEN105THENGEHALT*1 5WHEN161THENGEHALT*1
----------------------------------------------------------------------------------------------------
167000
Ich war der Meinung, Du brauchst die Summen für Boden- bwz Flugpersonal getrennt?
Gruß!
nefu
Einfachere Lösung für die ursprüngliche Aufgabenstellung, falls es noch interessiert:
scott@TEST102 >SELECT SUM((CASE NVL(f.dienstrangid, 0)
2 WHEN 0 THEN 0
3 WHEN 100 THEN gehalt * 15
4 WHEN 105 THEN gehalt * 15
5 WHEN 110 THEN gehalt * 15
6 ELSE gehalt * 14
7 END)) SUMMFLUGP,
8 SUM((CASE NVL(abteilungsid, 0)
9 WHEN 0 THEN 0
10 WHEN 161 THEN gehalt * 12
11 ELSE gehalt * 14
12 END)) SUMMBODENP
13 FROM personal p
14 INNER JOIN gehaltsstufe g ON p.gehaltsstufeid = g.gehaltsstufeid
15 LEFT OUTER JOIN bodenpersonal b ON p.personid = b.personid
16 LEFT OUTER JOIN flugpersonal f ON p.personid = f.personid;
SUMMFLUGP SUMMBODENP
---------- ----------
87000 80000
scott@TEST102 >
ah danke für den hinweis mit den outer join!
ich habe hier dann auch noch die lösung des vortragenden
select 'Bodenpersonal' as Personaltyp,(sum(g1.monatsgehalt)*12+
sum(g2.monatsgehalt)*14) Gehalt from bodenpersonal b1
left outer join bodenpersonal b2 on b2.ABTEILUNGSID=161 and
b2.personid=b1.personid
left outer join bodenpersonal b3 on b3.ABTEILUNGSID<>161 and
b3.personid=b1.personid
left outer join personal p1 on p1.personid=b2.personid
left outer join personal p2 on p2.personid=b3.personid
left outer join gehaltsstufe g1 on g1.gehaltsstufeid=p1.gehaltsstufeid
left outer join gehaltsstufe g2 on g2.gehaltsstufeid=p2.gehaltsstufeid
union
select 'Flugpersonal',(sum(g1.monatsgehalt)*15+ sum(g2.monatsgehalt)*14)from
flugpersonal f1
left outer join flugpersonal f2 on (f2.dienstrangid=100 or
f2.dienstrangid=110 or f2.dienstrangid=105) and f2.personid=f1.personid
left outer join flugpersonal f3 on (f3.dienstrangid=210 or
f3.dienstrangid=501) and f3.personid=f1.personid
left outer join personal p1 on p1.personid=f2.personid
left outer join personal p2 on p2.personid=f3.personid
left outer join gehaltsstufe g1 on g1.gehaltsstufeid=p1.gehaltsstufeid
left outer join gehaltsstufe g2 on g2.gehaltsstufeid=p2.gehaltsstufeid;
ich habe hier dann auch noch die lösung des vortragenden ...
Also auch mit UNION und einem extra outer join für jede CASE Unterscheidung.
In SQL gibt es ja meist viele Wege zum Ziel, und jeder hat seinen eigenen Programmierstil.
Wäre interessant, mal die Ausführungspläne der verschiedenen Varianten zu sehen.
Gruß!
nefu
sein "übungsziel" war ja möglichst viele joins einzubauen .... vorallem um subselects zu verhindern (gut wenn man etwas davon erfahren hätte bevor man die übung herzeigt...)
vBulletin®, Copyright ©2000-2024, Jelsoft Enterprises Ltd.