none
SSRS / SQL: Wie frage ich eine Artikel-Schnittmenge zwischen Tabelle A und B ab? RRS feed

  • Frage

  • Hallo,

    ich habe eine Tabelle mit Lagerverbauchsbuchungen mit N-Artikeln. Weiter habe ich eine andere Tabelle mit Lagerbeständen mit M-Artikeln.

    Per Lookup lasse ich die Lagerbestände der verbrauchten Artikel anzeigen, wobei mir jedoch die Artikel fehlen, die auf Lager sind. Die Abfrgabe muss also Artikel ergänzen wo es keine Verbräuche gibt 

    Das SQL bricht im Report mit folgender Fehlermeldung ab:
    Die nächste Datenzeile für das Lagerbuchungen-Dataset kann nicht gelesen werden. (rsErrorReadingNextDataRow)

    Das SQL lautet:

    SELECT
      Vw_FACT_Lagerbuchungen.ANR
    Vw_FACT_Lagerbuchungen.Menge

      
    FROM
      Vw_FACT_Lagerbuchungen

    where ........

    EXCEPT

    Select 

       1 as Menge
      ,Vw_FACT_Lagerbestand.ANR
     
    FROM
      Vw_FACT_Lagerbestand

    Was läuft hier schief? Auf dem SQL-Server kommt zwar kein Fehler, aber das Ergebnis ist auch nicht richtig. Welche Lösungsansätze gibt es also eine Kombination aus inner und outerjoin?

    Vielen Dank.

    Gruß Christian




    Dienstag, 8. September 2020 10:27

Alle Antworten

  • Hallo,

    ich mache so etwas mit einem outer join - Ich gehe davon aus, dass ANR der Primärschlüssel der Tabellen ist - Dann versuche mal:

    SELECT
      Vw_FACT_Lagerbuchungen.ANR
    Vw_FACT_Lagerbuchungen.Menge

      
    FROM
      Vw_FACT_Lagerbuchungen
    left outer join Vw_FACT_Lagerbestand on Vw_FACT_Lagerbestand.ANR = Vw_FACT_Lagerbuchungen.ANR

    where Vw_FACT_Lagerbestand.ANR is null

    Vw_FACT_Lagerbestand.ANR wird nur null, wenn der outer join nichts findet.

    Gruß

    Willi

    Dienstag, 8. September 2020 11:01
  • Vielen Dank für den Ansatz,

    mit der Frage/Annahme ist der Punkt getroffen:

    ANR ist nur der Primärschlüssel in der Bestandsdatei. Dort sind zur Zeit 8095 Die Artikelanzahl kann täglich variieren. Die Buchungsdatei kommt der Artikel zig mal je Buchung vor, je nach Eingrenzung des Zeitraumes oder anderen Kriterien

    Die Buchungsdatei grenze ich über div. Kritieren ein, so dass sich hier die Schnittmenge ändert. Die Herausforderung ist, wie man hier die fehlenden Bestandsartikel dann ergänzen kann.

    Idee:

    Ich mache auf dem Server eine Bestandsdatei mit je einer Lagerbuchungen "0" je Monat&Jahr und hänge diese als Union als Dummy dran. Lässt sich dies als left outer join ev. eleganter lösen? 


    Dienstag, 8. September 2020 11:42
  • select a.ANR, b.Bestand, l.Datum, l.Menge
    from Artikel a left join Bestand b on a.ANR = b.ANR left join Lagerbewegung L on A.ANR = L.ANR where coalesce(b.Bestand, 0) <> 0 or coalesce(l.Menge, 0) <> 0

    Dein Artikelstamm ist der Start, da hier alle Artikel überhaupt vorhanden sind.
    Dazu bindest du die Bestände und die Bewegungen per left join, falls keine Information da ist.
    Mittels Where-Klausel prüfe ich ob überhaupt relevante Mengen zum Artikel vorhanden sind.
    In den Left-Joins kannst du zusätzliche Ausschlüsse (z.B. Buchungsdatum, o.ä.) mit hineinnehmen (Achtung: nicht in die Where-Klausel da das sonst zum Inner-Join wird).

    Dienstag, 8. September 2020 12:07
  • Hallo zusammen,

    danke für den Klasse Lösungsansatz. Das ist genau was ich meine. Leider bekomme ich die Kurve nicht, wie ich a und b als "Select" von der Syntax her am Stück verbinde und einfüge. Für b gibt es ja kein "FROM b". wobei b dann das (SELECT.....) ist. Die Abfragen einzeln laufen.

    (a ergibt 660 Artikel, b ergibt 220 Artikel und L ergibt 85 Artikel)

    SELECT        a.ANR
    FROM            (SELECT        ANR
                              FROM            dbo.Vw_DIM_Artikel
                              WHERE        (LEFT(ANR, 1) = 'M')) AS a

    SELECT        b.ANR
    FROM            (SELECT        ANR
                              FROM            dbo.Vw_FACT_Lagerbestand
                              WHERE        (LEFT(ANR, 1) = 'M')
                              GROUP BY ANR) AS b



    SELECT        ANR, Menge
    FROM            (SELECT        ANR, Menge
                              FROM            dbo.Vw_FACT_Lagerbuchungen
                              WHERE        (LEFT(ANR, 1) = 'M') AND (Jahr = 2020) AND (Monat = 4)) AS L

    Vielen Dank und viele Grüße

    Chris


    Mittwoch, 9. September 2020 07:55
  • SELECT        a.ANR, b.Lagerbestand, l.Menge
    
    FROM            (SELECT        ANR
                              FROM            dbo.Vw_DIM_Artikel
                              WHERE        ANR like 'M%')
     a
    
    left join (SELECT        ANR, sum(Lagerbestand) Lagerbestand
                              FROM  dbo.Vw_FACT_Lagerbestand) 
                              WHERE  ANR like 'M%'
                              GROUP BY ANR)
     b on a.anr = b.anr
    
    
    left join (SELECT        ANR, Menge
                              FROM dbo.Vw_FACT_Lagerbuchungen
                              WHERE (ANR like 'M%') AND (Jahr = 2020) AND (Monat = 4)) 
    L on a.anr = l.anr
    
    where coalesce(b.Lagerbestand) <> 0
    and coalesce(l.Menge, 0) <> 0

    Da du den Bestand ja auch haben willst, solltest du das Feld auch summieren, da du Group by verwendest.
    Wobei ich mich dabei frage, ob du mehr als einen Bestandsatz hast (z.B. nach Lager).

    Mittwoch, 9. September 2020 08:09
  • Hallo und erstmal 1000 Dank,

    beim Bestand bin ich über die selbe Frage gestolpert! Sorry für die fehlende Info. Ein M-Artikel hat leider genau in einem Fall mehrere Lagerorte, also müsste man da über den Artikel gruppieren und die Bestandsmenge summieren. 

    Das Select habe ich inhaltlich vom Ansatz verstanden, doch es erzeugt 2 Fehlerstufen:

    1.) Fehler in FROM Klausel in der Nähe von "where" da der Abfragetext nicht analysiert werden kann

    Anmerkung von mir: Verstehe ich nicht, da der Part einzeln getestet einwandfrei läuft.

    2.) Falsche Syntax in der Nähe des WHERE Syntax von "L"

    Anmerkung von mir: Verstehe nicht ganz, warum man scheinbar einfach das "AS" weglassen kann, wie "AS b" oder "as Lagerbestand" was ergänzt aber nichts bewirkt und scheinbar vom Server automatisch korrigiert wird. 

    FAZIT: Dank des Beispiels verstehe ich jetzt die Nutzung der Unterabfragen besser, aber den Fehler der verursacht nicht.

    Vielen Dank für die Klasse Tipps und Hilfen!


    Mittwoch, 9. September 2020 09:46
  • .....lösche ich die Klammer hinter "Lagerbestand)" erzeugt der Editor zwar ein neues Format, meckert aber weiter mit einem Fehler in der Nähe der ")" . Setzte ich die Klammer wieder meckert er Fehler in WHERE der Nähe vom "AS". Sieht aber eigentlich doch jetzt gut aus!?

    SELECT        a.ANR, b.Lagerbestand, L.Menge
    FROM            (SELECT        ANR
                              FROM            dbo.Vw_DIM_Artikel
                              WHERE        (ANR LIKE 'M%')) AS a LEFT OUTER JOIN
                                 (SELECT        ANR, SUM(b.Lagerbestand) AS Lagerbestand
                                   FROM            dbo.Vw_FACT_Lagerbestand)
                                   WHERE        (ANR LIKE 'M%')
                                   GROUP BY ANR) AS b ON a.ANR = b.ANR LEFT OUTER JOIN
                                 (SELECT        ANR, Menge
                                   FROM            dbo.Vw_FACT_Lagerbuchungen
                                   WHERE        (ANR LIKE 'M%') AND (Jahr = 2020) AND (Monat = 4)) AS L ON a.ANR = L.ANR
    WHERE        (COALESCE (b.Lagerbestand,) <> 0) AND (COALESCE (L.Menge, 0) <> 0)

    Mittwoch, 9. September 2020 09:55
  • Bearbeite die SQL's mal besser im Code-Fenster, dann sind sie besser zu lesen.

    Lass man die "as" einfach weg. DIe sind nicht nötig.

    SELECT        a.ANR, b.Lagerbestand, L.Menge
    FROM            
    (SELECT        ANR
       FROM            dbo.Vw_DIM_Artikel
      WHERE        (ANR LIKE 'M%')
    ) a 
    
    LEFT OUTER JOIN
    (SELECT   ANR, SUM(b.Lagerbestand) Lagerbestand
       FROM   dbo.Vw_FACT_Lagerbestand)
      WHERE        (ANR LIKE 'M%')
      GROUP BY ANR
    ) b ON a.ANR = b.ANR 
    
    LEFT OUTER JOIN
    (SELECT  ANR, Menge
       FROM  dbo.Vw_FACT_Lagerbuchungen
       WHERE (ANR LIKE 'M%') AND (Jahr = 2020) AND (Monat = 4)
    ) L ON a.ANR = L.ANR
    
    WHERE(COALESCE (b.Lagerbestand, 0) <> 0) 
    AND (COALESCE (L.Menge, 0) <> 0)

    Feldnamen sind natürlich entsprechend zu korrigieren;-)

    Mittwoch, 9. September 2020 10:45
  • Danke für die Rückmeldung!

    Mit der Editorsicht wird es doch deutlich lesbarer und prüfbarer. Welchen nutzt du?

    Mit dem Namen habe ich jetzt einheitlich gemäß Basistabellen umgeändert. Die SQLs laufen einzeln betrachtet also. Fehler weiter bei WHERE in der Nähe von "L" selbst wenn ich blockweise bis zum Ende where und group by rausnehme kommt dann Fehler in der Nähe von ")" :( 

    (SELECT        ANR
       FROM            dbo.Vw_DIM_Artikel
      WHERE        (ANR LIKE 'M%')
    ) a 

    LEFT OUTER JOIN
    (SELECT   ANR, SUM(b.Bestandsmenge) Bestandsmenge
       FROM   dbo.Vw_FACT_Lagerbestand)
      WHERE        (ANR LIKE 'M%')
      GROUP BY ANR
    ) b ON a.ANR = b.ANR 

    LEFT OUTER JOIN
    (SELECT  ANR, Menge
       FROM  dbo.Vw_FACT_Lagerbuchungen
       WHERE (ANR LIKE 'M%') AND (Jahr = 2020) AND (Monat = 4)
    ) L ON a.ANR = L.ANR

    WHERE(COALESCE (b.Bestandsmenge, 0) <> 0) 
    AND (COALESCE (L.Menge, 0) <> 0)

    Erstelle ich 3 Abfragen a,b und L inkl. der Where-Einschränkung und group by und lasse das SQL laufen geht es allerdings. 

    SELECT        dbo.a.ANR, dbo.b.Bestandsmenge, dbo.L.Menge
    FROM            dbo.a LEFT OUTER JOIN
                             dbo.L ON dbo.a.ANR = dbo.L.ANR LEFT OUTER JOIN
                             dbo.b ON dbo.a.ANR = dbo.b.ANR
    WHERE        (COALESCE (dbo.L.Menge, 0) <> 0) AND (COALESCE (dbo.b.Bestandsmenge, 0) <> 0)

    Habe ich Tomaten auf den Augen und find den Fehler nicht:

    Vielen Dank für Eure Hilfe!









    Mittwoch, 9. September 2020 12:35
  • Die Tomaten sind hier:

    LEFT OUTER JOIN
    (SELECT   ANR, SUM(Bestandsmenge) Bestandsmenge
       FROM   dbo.Vw_FACT_Lagerbestand
      WHERE        (ANR LIKE 'M%')
      GROUP BY ANR
    ) b ON a.ANR = b.ANR 

    "b" kann innerhalb nicht verwendet werden und nach dem Tabellenname ist eine Klammer zu viel.
    Du kannst also die Einzel-SQL's nicht aus diesem SQL haerauskopiert haben;-).

    Der "Editor" ist in in diesem der Knop links neben "HTML".

    Mittwoch, 9. September 2020 14:10
  • Hallo und vielen Dank für die Rückmeldung und Hilfe. Ich bekomme leider noch nicht die Kurve:

    Die Klammer hatte ich in der Tat gelöscht (jetzt sollte es aber mit dem rauskopieren gehen), aber was meinst du mit "b" (und wahrscheinlich auch "l") kann ich nicht innerhalb verwenden? Wie wäre es denn richtig?

    Knop neben HTML. Auch hier bin ich leicht begriffstutzig da ich neben "html" nicht zuordnen kann.


    (SELECT        ANR
       FROM            dbo.Vw_DIM_Artikel
      WHERE        (ANR LIKE 'M%')
    ) a 


    LEFT OUTER JOIN
    (SELECT   ANR, SUM(b.Bestandsmenge) Bestandsmenge
       FROM   dbo.Vw_FACT_Lagerbestand
      WHERE        (ANR LIKE 'M%')
      GROUP BY ANR
    ) b ON a.ANR = b.ANR 


    LEFT OUTER JOIN
    (SELECT  ANR, Menge
       FROM  dbo.Vw_FACT_Lagerbuchungen
       WHERE (ANR LIKE 'M%') AND (Jahr = 2020) AND (Monat = 4)
    ) L ON a.ANR = L.ANR


    WHERE(COALESCE (b.Bestandsmenge, 0) <> 0) 
    AND (COALESCE (L.Menge, 0) <> 0)

    Gruß Chris



    Donnerstag, 10. September 2020 15:07
  • Wenn du hier postest hast du in diesem Editor eine Toolbox. Der 2. Button von rechts öffnet ein Code-Fenster der den Text so lässt wie du ihn eingibst. Zusätzlich kann je nach Typ der Code auch eingefärbt werden und ist leichter lesbar.

    Du scheinst bezüglich SQL eher rudimentäre Kenntnisse zu besitzen;-).
    Kurz erklärt:

    select * from 
    (
      select f1, f2, f3
        from mytable
        where ...
    ) a
    

    Innerhalb der Klammern ist ein separater Select dem du nach der Klamme einen Alias verpasst.
    Dieser Alias ist innerhalb der Klammer nicht bekannt und kann daher nicht verwendet werden.

    Und nun schau dir deinen SQL mal genau an oder gehe mal SQL-Tutorials durch.
    Da ich deine Tabellen nicht habe, kann ich deinen SQL auch nicht bauen.

    Donnerstag, 10. September 2020 16:01
  • Hallo Chris,

    ich schreibe so etwas lieber mit CTEs, da es dann übersichtlicher wird.
    Überflüssige Klammern lasse ich weg.

    Siehe auch: WITH common_table_expression (Transact-SQL)

    WITH a as
    (SELECT        ANR
       FROM            dbo.Vw_DIM_Artikel
      WHERE        ANR LIKE 'M%'
    ),
    b AS
    (SELECT   ANR, SUM(b.Bestandsmenge) Bestandsmenge
       FROM   dbo.Vw_FACT_Lagerbestand
      WHERE   ANR LIKE 'M%'
      GROUP BY ANR
    ),
    L AS 
    (SELECT  ANR, Menge
       FROM  dbo.Vw_FACT_Lagerbuchungen
       WHERE ANR LIKE 'M%' 
       AND Jahr = 2020 
       AND Monat = 4
    )
    
    SELECT a.ANR, b.Bestandsmenge, L.Menge
    FROM a
    LEFT OUTER JOIN
     b ON a.ANR = b.ANR 
    LEFT OUTER JOIN
     L ON a.ANR = L.ANR
    
    WHERE COALESCE (b.Bestandsmenge, 0) <> 0 
    AND COALESCE (L.Menge, 0) <> 0;
    Damit solltest Du einen besseren Überblick haben und den Fehler schneller finden können. 
    Aber wie schon von anderen angemerkt, können wir ohne Repro-Skripte hier nur bedingt weiterhelfen! 


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 11. September 2020 05:37
    Beantworter
  • Hallo zusammen,

    erstmal ein dickes Danke für die Hilfe und die Antworten!!!!! Hat jetzt geklappt, schwere Geburt, aber der Patient lebt und hat jetzt für die Zukunft mehr Durchblick :-) Das "b.Bestandsmenge" habe ich übersehen und die Schreibweise mittels CTEs macht das Ganze noch übersichtlicher und verständlicher. 

    Warum der SQL-Server teilweise gespeicherte Formate nicht behält ist mir unklar, genauso wenig warum die vorgeschlagene Einschränkung mit Nullwerte der Spalte "Bestandsmenge" ODER "Menge" komplett rausfiltern. Hätte es wiegewünscht erwartet. Mit "case" ging es dann, doch der Bericht ist deutlich langsamer als vorher.

    WHERE COALESCE (b.Bestandsmenge, 0) <> 0 AND COALESCE (L.Menge, 0) <> 0

    WHERE        (CASE WHEN L.Menge IS NULL THEN 0 ELSE L.Menge END + CASE WHEN b.Bestandsmenge IS NULL THEN 0 ELSE b.Bestandsmenge END <> 0) 


    Montag, 14. September 2020 06:58
  • Was die Geschwindigkeit angeht, so kann man mit Indexerstellung der Tabellen durchaus Verbesserungen erreichen.
    I.d.R. reicht es die Felder der Where-Klauseln in den Tabellen zu indizieren.
    Montag, 14. September 2020 07:31