Hilfe bei Abfrage (case when...)

[max050180]

Hallo miteinander,

ich bin neu hier im Forum und hoffe, ihr könnt mir bei einer Abfrage weiterhelfen.

Die folgende Abfrage liefert mir als Ergebnis zu einer ID drei Zeilen mit Ergebnissen:

SELECT distinct
  w.ID
  ,wavw.waf_meas_Type
  ,wavw.Thk_Cen
  ,wavw.WAF_FLAG
  ,wavl.operation
FROM

  LOADER.WAFERS w
  ,LOADER.LWWAV_WAFERS wavw
  ,LOADER.LWWAV_LOTS wavl

WHERE 1=1
  and w.id IN ('MW2034806055')
  and w.sid = wavw.waf_sid(+)
  and wavw.lothis_sid = wavl.LOTHIS_SID(+)

ID........................WAF_MEAS_TYPE............THK_CEN............ WAF_FLAG............OPERATION
MW2034806055...SPC_MS..........................885,36......................U..................................2180
MW2034806055...TST..................................885,32......................U..................................2185
MW2034806055...TST..................................885,32......................U..................................2470

Ich möchte allerdings pro ID nur eine Zeile angezeigt bekommen. Dazu habe ich den Code folgendermaßen geändert:

SELECT distinct
  w.ID WaferId
  ,max( case when wavw.WAF_FLAG in ('U','F') and wavl.operation = '2180' and wavw.Waf_Meas_Type NOT IN ('INTERPOL','TST') then wavw.waf_meas_Type else
    case when wavl.operation = '2180' and wavw.Waf_Meas_Type IN 'TST' then wavw.waf_meas_Type else
    case when wavw.WAF_FLAG in ('U','L') and wavl.operation = '2185' and wavw.Waf_Meas_Type IN 'TST' then wavw.waf_meas_Type else null end end end) Meastype
  ,max( case when wavw.WAF_FLAG in ('U','F') and wavl.operation = '2180' and wavw.Waf_Meas_Type NOT IN ('INTERPOL','TST') then wavw.Thk_Cen else
    case when wavl.operation = '2180' and wavw.Waf_Meas_Type IN 'TST' then wavw.Thk_Cen else
    case when wavw.WAF_FLAG in ('U','L') and wavl.operation = '2185' and wavw.Waf_Meas_Type IN 'TST' then wavw.Thk_Cen else null end end end) Thk_Cen
FROM
  LOADER.WAFERS w
  ,LOADER.LWWAV_WAFERS wavw
  ,LOADER.LWWAV_LOTS wavl

WHERE 1=1
  and w.id IN ('MW2034806055')
  and w.sid = wavw.waf_sid(+)
  and wavw.lothis_sid = wavl.LOTHIS_SID(+)
group by
  w.ID

Als Ergebnis erhalte ich wie gewünscht eine Zeile für die ID:
ID........................WAF_MEAS_TYPE............THK_CEN............
MW2034806055...TST ..........................885,36

Allerdings erhalte ich nicht das von mir gewünschte Ergebnis.Ich bin bislang davon ausgegangen, dass die Ausdrücke max(case when...) der Reihe nach abarbeitet und sobald eine Übereinstimmung vorliegt, diese abbricht und den entsprechenden Wert ausgibt. Das heißt, als Ergebnis erwarte ich:

ID.........................WAF_MEAS_TYPE............THK_CEN............
MW2034806055...SPC_MS..........................885,36

Wie müsste ich die Abfrage ändern, um das gewünschte Ergebnis zu erhalten?

Gruß, Matthias

 

[akretschmer]

Du könntest mit row_number() zählen (partition by id order by thk_cen desc) und außen rum nur die mit row_number = 1 auswählen. In PostgreSQL, nebenbei, gibt es DISTINCT ON(...), was Dein Problem eleganter löst, aber das hast Du ja nicht.

 

[max050180]

Hallo akretschmer,
danke für die schnelle Antwort. Könntest du mir das anhand meines Codes zeigen. Mit der von dir vorgeschlagenen Idee habe ich mich, wenn ich ehrlich bin, noch nicht beschäftigt - sprich, ich hab keine Ahnung wie ich das anstellen soll.

Gruß, Matthias

 

[akretschmer]

test=# create table max05(id int, val int);
CREATE TABLE
test=*# copy max05 from stdin;
Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.
>> 1   10
>> 1   20
>> 1   5
>> 2   100
>> 2   50
>> 2   300
>> 3   1
>> 3   2
>> 3   3
>> \.
COPY 9
test=*# with x as (select id, val, row_number() over (partition by id order by val) from max05 ) select id, val from x where row_number = 1;
 id | val
----+-----
  1 |  5
  2 |  50
  3 |  1
(3 Zeilen)

test=*# with x as (select id, val, row_number() over (partition by id order by val desc) from max05 ) select id, val from x where row_number = 1;
 id | val
----+-----
  1 |  20
  2 | 300
  3 |  3
(3 Zeilen)

test=*# select distinct on (id) id, val from max05 order by id, val;
 id | val
----+-----
  1 |  5
  2 |  50
  3 |  1
(3 Zeilen)

test=*# select distinct on (id) id, val from max05 order by id, val desc;
 id | val
----+-----
  1 |  20
  2 | 300
  3 |  3
(3 Zeilen)

test=*#

Rollback & Kaffee.

 

[ukulele]

TST 885,36 erhälst du bisher gar nicht, meinst du TST 885,32 oder willst du die Daten wirklich anders zusammen setzen?

Der CASE wird tatsächlich der Reihe nach abgearbeitet. Es muss also eine Bedingung nicht erfüllt sein. Ich denke also dein Ausgangsproblem ist vielleicht ein Denkfehler im CASE, ich kenne aber die Ausgangsdaten und die Zusammenhänge nicht.

 

[max050180]

Hallo ukulele,

zur Erklärung. Bei der ID handelt es sich um ein Teil was unter Umständen an mehreren Operationen gemessen wird oder werden kann. Es bekommt an der Operation einen Messtyp zugewiesen und ein Messflag.

Mit dem case-Ausdruck wollte ich der Reihe nach verschiedene Möglichkeiten abarbeiten und sobald die erste Übereinstimmung kommt, wird abgebrochen und der Wert der entsprechenden ID zugewiesen. Im Beispiel ist die erste Bedingung aus meiner Sicht erfüllt, d.h. wavw.WAF_FLAG in ('U','F') and wavl.operation = '2180' and wavw.Waf_Meas_Type NOT IN ('INTERPOL','TST') passt und somit müsste die ID, dann SPC_MS und die 835,36 ausgegeben werden. Die letzte Bedingung in dem Case-Teil stimmt auch wieder, aber ich bin davon ausgegangen, dass diese gar nicht mehr abgearbeitet wird, da bereits der erste Teil passt.

Gruß, Matthias

 

[ukulele]

Die erste und die letzte Bedigung im CASE schließen sich aber gegenseitig aus. Sowohl wavl.operation kann nicht gleichzeitig 2180 und 2185 sein und wavw.Waf_Meas_Type kann nicht gleichzeitig TST und nicht TST sein. Irgendwo steckt hier noch ein Denkfehler.

 

[max050180]

Das Teil mit der ID kann an den verschiedenen Operationen (hier im Beispiel 2180, 2185, 2470) gemessen werden. In dem Beispiel ist es eine Messung des gleichen Teiles an 2 verschiedenen Operationen. Einmal an Operation 2180 und dann noch mal an 2185. Bei der ersten Messung erhält die ID den Messtyp SPC_MS und das Flag 'U'. Bei der nächsten Messung erhält es den Messtyp TST und hier ebenfalls den Flag 'U', wobei es hier auch andere Typen geben kann. Es kann durchaus zu einer ID zwei Einträge geben mit unterschiedlichen Operationen und Flags.

 

[ukulele]

Ah jetzt verstehe ich was du meinst.

Das CASE bezieht sich aber immer auf den jeweiligen Einzeldatensatz, also auf lediglich einen von zwei oder mehr Messpunkten. Erst nach dem CASE wird aggregiert oder gruppiert. Da du das für zwei Spalten machst können dort auch sehr unterschiedliche Sachen raus kommen also z.B. der String für Meastype aus dem einen Datensatz und die Zahl aus einem anderen Datensatz.

Anhand des Beispiels:
Datensatz 1 | id=0 | TST | 1
Datensatz 2 | id=0 | SPC_MS | 2

TST und SPC_MS sowie 1 und 2 sind das Ergebnis der CASE-Anweisung. Dann kommt das GROUP BY mit max().

Ergebnis:
id=0 | TST | 2

T ist größer S, 2 ist größer 1. Ich glaube das ergibt so keinen Sinn.

PS: Das DISTINCT am Anfang ist überflüssig.

 

[max050180]

Okay. Dank deiner Erklärung habe ich das verstanden. Gibt es dennoch eine Lösung für mein "Problem" ? Zum Beispiel mit dem Ansatz von akretschmer row_number()..., wobei ich nicht weiß, wie ich das umsetzen könnte.

Gruß, Matthias

 

[ukulele]

Ja ROW_NUMBER() könnte das tun was du willst. Ist etwas schwer weil ich die Ausgangsdatensätze nicht kenne und der SQL Code auch merkwürdige Syntax hat aber ich probiers mal:

SELECT   t.ID,
     t.WaferId,
     t.waf_meas_Type,
     t.Thk_Cen
FROM   (

SELECT   ROW_NUMBER() OVER (PARTITION BY w.id ORDER BY
     (   CASE
       WHEN   wavw.WAF_FLAG IN ('U','F')
       AND     wavl.operation = '2180'
       AND     wavw.Waf_Meas_Type NOT IN ('INTERPOL','TST')
       THEN   1
       WHEN   wavl.operation = '2180'
       AND     wavw.Waf_Meas_Type IN ('TST')
       THEN   2
       WHEN   wavw.WAF_FLAG IN ('U','L')
       AND     wavl.operation = '2185'
       AND     wavw.Waf_Meas_Type IN ('TST')
       THEN   3
       ELSE   9
       END )) AS sortierung,
     w.ID,
     WaferId,
     wavw.waf_meas_Type,
     wavw.Thk_Cen
FROM   LOADER.WAFERS w
INNER JOIN LOADER.LWWAV_WAFERS wavw
ON     w.[sid] = wavw.waf_sid
INNER JOIN LOADER.LWWAV_LOTS wavl
ON     wavw.lothis_sid = wavl.LOTHIS_SID
WHERE   w.ID = 'MW2034806055'

     ) t
WHERE   t.sortierung = 1
ORDER BY t.ID

 

[max050180]

Hallo ukulele,
danke. Es waren noch 2 kleine "Fehler" drin, aber ohne das Hintergrundwissen zu den Ausgangsdatensätzen konntest du das auch nicht wissen. Was meinst du mit merkwürdiger Syntax? Ein Unterschied zu deiner Idee ist, dass du die JOINs im FROM-Teil untergebracht hast und ich diese im WHERE-Teil mit dem (+). Das könnte daran liegen, dass ich mir dies von Kollegen abgeschaut habe, die ebenfalls mit Oracle arbeiten, wobei ich mich als "Einsteiger" bezeichnen würde.

Vielleicht kannst du mir noch weiterhelfen, damit ich mein "Gesamtwerk" richtig umsetzen kann. Ich habe mal einen größeren Code eingefügt, der den oben erarbeiteten Teil enthalten soll. (Bitte nicht an den Bezeichnungen stören).

SELECT distinct
   
  w.ID as ID
  ,singl.LOT_NUM as LOS
  ,singw.LOT_NUM_OUT as KAS_1
  ,singslo.num as REIHE
 
  ,max( case when wavw.WAF_FLAG in ('U','F') and wavl.operation = '2180' and wavw.Waf_Meas_Type NOT IN ('INTERPOL','TST') then wavw.waf_meas_Type else
    case when wavl.operation = '2180' and wavw.Waf_Meas_Type IN 'TST' then wavw.waf_meas_Type else
    case when wavw.WAF_FLAG in ('U','L') and wavl.operation = '2185' and wavw.Waf_Meas_Type IN 'TST' then wavw.waf_meas_Type else null end end end) as Meastype
 
  ,max( case when wavw.WAF_FLAG in ('U','F') and wavl.operation = '2180' and wavw.Waf_Meas_Type NOT IN ('INTERPOL','TST') then wavw.Thk_Cen else
    case when wavl.operation = '2180' and wavw.Waf_Meas_Type IN 'TST' then wavw.Thk_Cen else
    case when wavw.WAF_FLAG in ('U','L') and wavl.operation = '2185' and wavw.Waf_Meas_Type IN 'TST' then wavw.Thk_Cen else null end end end) as Thk_Cen
   
  ,max(case when adew.WAF_FLAG in ('U','L') and adel.operation = '5040' then adel.LOT_NUM else
  case when adew.WAF_FLAG in ('A') and adel.operation = '5080' then adel.LOT_NUM else null end end) KAS_2
  ,max(case when adew.WAF_FLAG in ('U','L') and adew.WAF_FLAG_CONTEXT = 'GEO-RC1' and adel.operation = '5040' then adew.WARP else
  case when adew.WAF_FLAG in ('A') and adel.operation = '5080' then adew.WARP else null end end) As WARP
 
  ,w.ingot_seg as TEIL_ID
  ,w.INGOT_POS_MM as TEIL_MM
 
FROM
  LOADER.WAFERS w
  ,LOADER.LWSING_LOTS singl
  ,LOADER.LWSING_WAFERS singw
  ,LOADER.SLOTS singslo
  ,LOADER.LWWAV_WAFERS wavw
  ,LOADER.LWWAV_LOTS wavl

  ,LOADER.LWADE_LOTS adel
  ,LOADER.LWADE_WAFERS adew
 
WHERE
  singl.lot_num IN ('KBLZA')
  and w.sid = singw.waf_sid
  and singw.lothis_sid = singl.lothis_sid
  and singw.SLOT_SID_out = singslo.sid
  and w.sid = wavw.waf_sid(+)
  and wavw.lothis_sid = wavl.LOTHIS_SID(+)
  and w.sid = adew.waf_sid(+)
  and adew.lothis_sid = adel.lothis_sid(+)

group by w.ID, singl.LOT_NUM, singw.LOT_NUM_OUT, singslo.num ,w.INGOT_POS_MM ,w.ingot_seg 

order by w.INGOT_POS_MM

Zum Verständnis versuche ich mal eine Erklärung:

Es gibt ein großes Teil hier mit KBLZA bezeichnet. Aus diesem Teil werden viele kleine Teile hier mit ID bezeichnet. Diese landen dann in Kassetten mit verschiedenen Reihen KAS_1 und R. Dann kommen u.U. die "bekannten" Messungen mit Messtyp und Flag. Dann geht's weiter in einer weiteren Kassette KAS_2 mit einer weiteren Messung Warp. Dazu gibt es noch eine Teile_ID und Teil_MM.

Als Ergebnis möchte ich in etwa folgende Tabelle erhalten. Und hier kommt jetzt das "Problem". Wie kombiniere ich den Teil mit der "row_number" und dem von mir eingefügten größeren SQL-Code. D.h. ich möchte zu jeder ID, den Messtyp & THK_CEN mit der Sortierung =1. Wenn keine Messung erfolgt ist, dann soll die entsprechende Zelle leer bleiben. Das gleiche benötige ich dann für die Messung WARP. Liegt hier eine Messung vor, dann Wert einfügen an sonst leer lassen, wobei auch eine Mehrfachmessung möglich ist und ich ebenfalls eine Einschränkung mit "row_number" benötige. In dem Code müssen also (irgendwie) die Teile mit max(case...) ersetzt werden.

ID.........................|...TEIL.......|...KAS_1.............|...R...|MEASTYPE|.THK_CEN|...KAS_2............|...WARP...|...TEIL_ID.......|...TEIL_MM
MW2034816963...|...KBLZA...|...KBLZA-0B01...|...1...|...QUA_MS...|...898,93...|...KBLZA-0D01...|...............|...363113/095...|...865
MW2034816962...|...KBLZA...|...KBLZA-0B01...|...2...|.....................|.................|...KBLZA-0D02...|...............|...363113/095...|...866
MW2034816961...|...KBLZA...|...KBLZA-0B01...|...3...|...SPC_MS...|...900,78...|...KBLZA-0D03...|...10,51...|...363113/095...|...868
MW2034816960...|...KBLZA...|...KBLZA-0B01...|...4...|...SPC_MS...|...900,98...|...KBLZA-0D05...|...............|...363113/095...|...869
MW2034816959...|...KBLZA...|...KBLZA-0B01...|...5...|.....................|.................|...KBLZA-0D05...|...............|...363113/095...|...870
MW2034816888...|...KBLZA...|...KBLZA-0A03...|...1...|...TST...........|...905,2.....|...KBLZA-0D02...|...............|...363113/095...|...871
MW2034816887...|...KBLZA...|...KBLZA-0A03...|...2...|.....................|.................|...KBLZA-0D02...|...12,21...|...363113/095...|...872
MW2034816958...|...KBLZA...|...KBLZA-0B01...|...6...|...MEAS...|...900,98.......|...KBLZA-0D01...|...............|...363113/095...|...873

Vielen Dank schon mal im Voraus,
Gruß, Matthias

 

[ukulele]

Was meinst du mit merkwürdiger Syntax? Ein Unterschied zu deiner Idee ist, dass du die JOINs im FROM-Teil untergebracht hast und ich diese im WHERE-Teil mit dem (+). Das könnte daran liegen, dass ich mir dies von Kollegen abgeschaut habe, die ebenfalls mit Oracle arbeiten, wobei ich mich als "Einsteiger" bezeichnen würde.

Nun das eine sind sicherlich die Joins die ich lieber explizit beschreibe und das kann ich auch nur jedem empfehlen. Ich hab noch nie mit Oracle gearbeitet und kenne es nur hier aus dem Forum, (+) ist mir nicht bekannt. Du postest aber auch unter MSSQL, daher gehe ich mal davon aus das du jetzt grade MSSQL abfragst.

Abgesehen davon hatte ich ja schon das DISTINCT als überflüssig ausgemacht. Auch fehlt eigentlich ein Klammerung bei einigen Waf_Meas_Type IN 'TST' Bedingungen, die werden bei mir angemeckert. Das 1=1 hinterm WHERE ist sinnlos, aber das kommt vermutlich alles vom reduzieren des Codes.

Dein CASE-Teil ist eher umständlich. Du kannst mehrere WHERE Bedingungen hintereinander abarbeiten und musst nicht mehrere CASE-Befehle verschachteln wie WENN-Funktionen bei Excel.


Zu deinem umfangreicheren Code:
Sich bei sovielen kryptischen Abkürzungen und fachlich spezifischen Bezeichnungen zurecht zu finden ist nicht ganz einfach und auch aufwendig. Daher würde ich dir mal einen Pseudo-Code schreiben wie ich das angehen würde, das müsstest du verstehen und dann erweitern.

SELECT   h.ID,
     h.TEIL,
     h.KAS_1,
     h.R,
     u1.MEASTYPE,
     u1.THK_CEN,
     h.KAS_2
     u2.WARP,
     h.TEIL_ID,
     h.TEIL_MM
FROM   hauptquery h --Kann mehrere Joins beinhalten aber eben nur das was für die Spalten nötig ist. GROUP BY oder CASE dürfte nicht erforderlich sein.
LEFT JOIN (

SELECT   ROW_NUMBER() OVER (PARTITION BY ID,KAS_1,R ORDER BY deine_spalten_oder_CASE_oder_wie_auch_immer) AS zeile, --ROW_NUMBER() unterteilt erst nach den Fremdschlüsseln und sortiert dann nach beliebigen Bedingungen.
     ID,
     KAS_1,
     R,
     MEASTYPE,
     THK_CEN
FROM   unterquery1 --Das Unterquery liefert alle Messwerte, die die Bedingungen erfüllen

     ) u1
ON     h.ID = u1.ID
AND     h.KAS_1 = u1.KAS_1
AND     h.R = u1.R
AND     u1.zeile = 1 --Egal wie viele Messwerte es gibt zu der ID, der Kassette und der Reihe es wird immer nur maximal die erste gejoint und dadurch hast du auch nur einen Datensatz.
LEFT JOIN (

SELECT   ROW_NUMBER() OVER (PARTITION BY ID,KAS_2,R ORDER BY deine_spalten_oder_CASE_oder_wie_auch_immer) AS zeile,
     ID,
     KAS_2,
     R,
     WARP
FROM   unterquery2

     ) u2
ON     h.ID = u2.ID
AND     h.KAS_2 = u2.KAS_2
AND     h.R = u2.R
AND     u2.zeile = 1
WHERE   h.TEIL = 'KBLZA' --Ich bin mir nicht sicher ob TEIL auch ein Schlüssel ist oder von ID abhängig.

Eigentlich sind das drei Abfragen die man bequem einzeln schreiben kann so das nur das nötigste drin steht und dann per Left Join zusammen setzt. Bei richtig großen Datenmengen wird eventuell die Performance fies, das wird man dann sehen.

 

[max050180]

Danke das Beispiel. Ich werde mich jetzt gleich mal ran machen und probieren.

Nun das eine sind sicherlich die Joins die ich lieber explizit beschreibe und das kann ich auch nur jedem empfehlen. Ich hab noch nie mit Oracle gearbeitet und kenne es nur hier aus dem Forum, (+) ist mir nicht bekannt. Du postest aber auch unter MSSQL, daher gehe ich mal davon aus das du jetzt grade MSSQL abfragst.

Also ich arbeite z.T. mit dem Oracle Discoverer 4.1.47. Aber zunehmend verwende ich zum Erstellen und Probieren den Oracle SQL Developer Version 4.1.5.21, um SQL-Abfragen dann direkt über Excel laufen zu lassen. Wenn jetzt MSSQL der "falsche" Ort zum posten ist, wo müsste ich meine Fragen zukünftig besser platzieren???

Auf alle Fälle habe ich jetzt von dir einiges gelernt. Zum Beispiel wusste ich bislang nicht, dass man bei partition by auch mehrere Spalten wie in deinem Beispiel ROW_NUMBER() OVER (PARTITION BY ID,KAS_1,R ORDER BY deine_spalten_oder_CASE_oder_wie_auch_immer)eingeben kann. Wenn ich es richtig verstehe, könnte ich über Partition by zuerst nach verschiedenen Sachen gruppieren und anschließend noch nach verschiedenen Kriterien sortieren, oder?

Gruß, Matthias

 

[ukulele]

Wenn jetzt MSSQL der "falsche" Ort zum posten ist, wo müsste ich meine Fragen zukünftig besser platzieren???

Also wenn die DB Oracle ist dann unter Oracle, wenn es eine MS SQL DB ist dann unter MS SQL. Oracle kann auf jedenfall alles hier genannte aber es gibt immer wieder Unterschiede in der Syntax. Wird aber in diesem Fall kein Problem sein.

Wenn ich es richtig verstehe, könnte ich über Partition by zuerst nach verschiedenen Sachen gruppieren und anschließend noch nach verschiedenen Kriterien sortieren, oder?

Korrekt. Du kannst PARTITON BY und ORDER BY auch noch bei rank(), lead() oder lag() verwenden.
OVER Clause (Transact-SQL)
Die Spalte die du damit erzeugst ist auch unabhängig von der eigentlichen Sortierung.