SQL Statements mit left join verbinden

[JudAD]

Hallo,

ich habe mal wieder das Problem, das ich Aggregatfunktion und left join nicht in einem statement hinbekomme. Ich bekomme das gewünschte Ergebnis nur wenn ich zwei statments über union verbinde.

Das ist das union statement das funktioniert:

select SPP1.Itemcode, MAX(SPP1.LINENUM)as MaxZahl
from OITM
join SPP1 on OITM.ItemCode = SPP1.ItemCode
where OITM.U_mtz is not NULL and OITM.U_KundenNr = '11889' and SPP1.ListNum = 1
group by SPP1.ItemCode
union
select OITM.ItemCode, -1 as MaxZahl
from OITM
where OITM.U_mtz is not NULL and OITM.U_KundenNr = '11889' and OITM.Itemcode not in (select Itemcode from SPP1 where SPP1.ListNum = 1)

Mein erster Versuch sah so aus, hat aber leider nicht das gewünschte Ergebnis gebracht:

select SPP1.Itemcode, CASE when MAX(SPP1.LINENUM) is NULL then -1 else MAX(SPP1.LINENUM) end as MaxZahl
from SPP1
left join OITM on OITM.ItemCode = SPP1.ItemCode
where OITM.U_mtz is not NULL and OITM.U_KundenNr = '11889' and SPP1.ListNum = 1
group by SPP1.ItemCode


Was mache ich falsch?

Danke vorab

Tayfun

 

[akretschmer]

was genau willst du denn erreichen? Kannst Du das anhand einer kleinen, fiktiven Tabelle mit 2-3 Zeilen und dem gewünschten Resultat zeigen? Aggregatsfunktionen im Case wird so wohl eher nix werden ...

 

[JudAD]

Hi, Ich habe 2 Tabellen. In der Tabelle OITM stehen zu jedem Itemcode nur ein Datensatz. In der Tabelle SPP1 können zu jedem Itemcode mehere Datensätze enthalten sein.

SPP1:
Itecmcode, LineNum, ListNum
233653, 0, 1
233653, 1, 1
233654, 0, 1
233654, 1, 1
233654, 2, 1
233655, 0, 1
233655, 1, 1
233658, 0, 1

OITM:
Itemcode, U_mtz, U_KundenNr
233653, 3, 11889
233654, 8, 11889
233655, NULL, 17589
233656, 5, 11889
233657, 4, 11889
233658, NULL, 15689

Ergebinis:
Itemcode, MaxZahl
233653, 1
233654, 2
233656, -1
233657, -1

Hilft das weiter ?

 

[akretschmer]

test=*# select * from spp1 ;
 itemcode | line | listnum
----------+------+---------
  233653 |  0 |  1
  233653 |  1 |  1
  233654 |  0 |  1
  233654 |  1 |  1
  233654 |  2 |  1
  233655 |  0 |  1
  233655 |  1 |  1
  233658 |  0 |  1
(8 rows)

test=*# select * from oitm ;
 itemcode | u_mtz
----------+-------
  233653 |  3
  233654 |  8
  233655 |   
  233656 |  5
  233657 |  4
  233658 |   
(6 rows)

test=*# select itemcode, coalesce(max(spp1.line),-1) from oitm left join spp1 using (itemcode) where oitm.u_mtz is not null group by 1 order by 1;
 itemcode | coalesce
----------+----------
  233653 |  1
  233654 |  2
  233656 |  -1
  233657 |  -1
(4 rows)

hilft das?

 

[JudAD]

Hmm,

leider nicht.

Wenn ich es etwas umschreibe (Where-Klausel & Exakte Bezeichnung line/linenum):

select itemcode, coalesce(max(spp1.linenum),-1)
from OITM
left join SPP1 using (itemcode)
where OITM.U_mtz is not NULL and OITM.U_KundenNr = '11889' and SPP1.ListNum = 1
group by 1
order by 1

und ausführe erhalte ich folgende Fehlermeldung:

Meldung 321, Ebene 15, Status 1, Zeile 3
"itemcode" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

Wenn ich es so umschreibe das ich es lesen kann, dann erhalte ich wieder nur das Ergebnis wie in meinem nicht funktionierenden statement.

select spp1.itemcode, coalesce(max(spp1.linenum),-1)
from OITM
left join SPP1 on OITM.ItemCode = SPP1.ItemCode
where OITM.U_mtz is not NULL and OITM.U_KundenNr = '11889' and SPP1.ListNum = 1
group by spp1.ItemCode
order by spp1.ItemCode

 

[akretschmer]

works for me:

test=*# select oitm.itemcode, coalesce(max(spp1.line),-1) from oitm left join spp1 on oitm.itemcode=spp1.itemcode where oitm.u_mtz is not null group by 1 order by 1;
 itemcode | coalesce
----------+----------
  233653 |  1
  233654 |  2
  233656 |  -1
  233657 |  -1
(4 rows)

 

[JudAD]

Hmm, keine Ahnung. Die Fehlermeldung stammt vom SQl-Server. Funkt es bei Dir auch wenn Du die Where-Klausel von mir übernimmst?

Insgesamt ist es auch nicht so dramatisch wenn ich es über union ausführe. Wollte es nur etwas vereinfachen.

Danke dennoch für die schnelle Hilfe & Mühe

 

[akretschmer]

jein, ich hab da ja Spalten weggelassen, wie z.B. die eine, die immer 1 ist. Das sollte aber egal sein.

 

[JudAD]

Die Listnum ist nicht immer 1, die kann auch den Wert 2 annehmen. Die Eingrenzung auf die bestimmte KDNr (11889) ist auch wichtig. Ich habe leider keine Ahnung was dann falsch ist.

Wie bereits gesagt - das ist nicht so wichtig - möchte Dich nicht unnötig von Deiner Arbeit abhalten.

 

[ukulele]

Also bei mir funktioniert dein erster Lösungsansatz, welche MSSQL Version hat dein Server? Was passiert wenn du das ausführst?

 

[JudAD]

Hi und danke für die Rückmeldung.

Es ist SQL Server Standard 2008R2 sp3 (EN)

Es wird schlichtweg das left vom left join ignoriert - aber das Ganze mit den original Tabellen. Meine Beispieltabellen und Datensätze habe ich nicht getestet. Werde ich aber die Tage mal testen. Habe heute und morge frei, deshalb erst am Mittwoch oder Donnerstag. Aber eigentlich sollte es keinen Unterschied machen mit welchen Tabellen.

Mit meinem Daten-Beispiel würden mit meinem ersten SQL-Statement ohne union folgendes Ergebnis kommen:

Itemcode, MaxZahl
233653, 1
233654, 2

Ich gebe noch Bescheid, wenn ich es nochmals teste

 

[JudAD]

Also bei mir funktioniert dein erster Lösungsansatz, welche MSSQL Version hat dein Server? Was passiert wenn du das ausführst?

Hi ukulele,

habe es nun mit meinen hier geposteten Testdaten in der selben DB getestet in der auch die original Tabellen liegen. Habe den Tabellen jeweils ein "t" im Tbellennamen vorangestellt

Ich bekomme folgende Ergebnisse:


-- UNION (funktioniert & Ergebnis korrekt bzw. wie erwartet)
select tspp1.Itemcode, MAX(tspp1.LINENUM)as MaxZahl
from toitm
join tspp1 on toitm.ItemCode = tspp1.ItemCode
where toitm.U_mtz is not NULL and toitm.U_KundenNr = '11889' and tspp1.ListNum = 1
group by tspp1.ItemCode
union
select toitm.ItemCode, -1 as MaxZahl
from toitm
where toitm.U_mtz is not NULL and toitm.U_KundenNr = '11889' and toitm.Itemcode not in (select Itemcode from tspp1 where tspp1.ListNum = 1)

Ergebnis:
Itemcode MaxZahl
233653 1
233654 2
233656 -1
233657 -1


-- LEFT JOIN (funktioniert aber liefert falsches Ergebnis)
select tspp1.Itemcode, CASE when MAX(tspp1.LINENUM) is NULL then -1 else MAX(tspp1.LINENUM) end as MaxZahl
from tspp1
left join toitm on toitm.ItemCode = tspp1.ItemCode
where toitm.U_mtz is not NULL and toitm.U_KundenNr = '11889' and tspp1.ListNum = 1
group by tspp1.ItemCode

Ergebnis:
Itemcode MaxZahl
233653 1
233654 2


-- akretschmer Version (Fehler)
select itemcode, coalesce(max(tspp1.line),-1) from toitm left join tspp1 using (itemcode) where toitm.u_mtz is not null group by 1 order by 1

-- oder angepasste akretschmer Version (Fehler)
select itemcode, coalesce(max(tspp1.linenum),-1)
from toitm
left join tspp1 using (itemcode)
where toitm.U_mtz is not NULL and toitm.U_KundenNr = '11889' and tspp1.ListNum = 1
group by 1
order by 1

Ergebnis:
Meldung 321, Ebene 15, Status 1, Zeile 1
"itemcode" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.


-- oder weiter angepasste akretschmer Version (funktioniert aber liefert falsches Ergebnis)
select tspp1.itemcode, coalesce(max(tspp1.linenum),-1)
from toitm
left join tspp1 on toitm.ItemCode = tspp1.ItemCode
where toitm.U_mtz is not NULL and toitm.U_KundenNr = '11889' and tspp1.ListNum = 1
group by tspp1.ItemCode
order by tspp1.ItemCode

Ergebnis:
itemcode (Kein Spaltenname)
233653 1
233654 2

Als Anhang kannst Du nochmals die Tabellen sehen

 

[ukulele]

Sagen wir mal so, ich kann das nicht reproduzieren oder eine Erklärung dazu liefern. Ich gehe fest davon aus die Ursache für das Verhalten deines Servers in den Daten liegt. Mir ist noch kein SQL Server (nichtmal mit MySQL) untergekommen, der sich bei einem Left Join "fehlerhaft" arbeitet und ich kenne keine Möglichkeit, das Verhalten durch Einstellungen zu verfälschen...

 

[JudAD]

Hast Du meine Daten und Scripts genau so verwendet wie im letzten Post von mir und bekommst andere Ergebnisse? Welche SQL Version hast Du?