Ergebnis auf einen Datensatz reduzieren

[HCB]

Hallo und Danke an alle die mir helfen wollen.
Ich habe 2 Queries die genau das machen was sie sollen, nur müsste das in einer Abfrage untergebracht werden.
Die erste Abfrage holt mir alle offenen Aufträge mit ABFDocVisType = 1 und ABFPos ON ABFDok.ABFDocAuftragID = ABFPos.ABFPosDocID und bringt mir u.a. die bestellte Menge. OK.
Die zweite Abfrage bringt mir die bereits gelieferten Mengen mit Lieferschein ABFDocVisType = 2 und SUM(ABFPosMenge) AS Geliefert und ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID.
Die Innerjoins sowie die ABFDocVisType sind unterschiedlich: 1 = Aufträge, 2 = Lieferschein, 3 = Rechnung etc.

Hier nochmal beide Abfragen:

Delphi-Quellcode:

SELECT
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    ABFPosMenge as bestellt,
    ABFPosArtNr,
    ABFPosName1
FROM
    ABFDok
INNER JOIN
    ABFPos ON ABFDok.ABFDocAuftragID = ABFPos.ABFPosDocID
WHERE
    ABFDocOptFertig = false AND
    ABFDocVisType = 1 AND
    ABFPosEPreis > 0 AND
    ABFPosType IN (0, 7) AND
    ABFPosNr <> ''
order by ABFDocAuftragNr;

Und hier die zweite:

Delphi-Quellcode:

SELECT
    ABFDocAuftragNr,
    ABFPosArtNr,
    SUM(ABFPosMenge) AS Geliefert
FROM
    ABFDok
INNER JOIN
    ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
WHERE
    ABFDocOptFertig = false AND
    ABFDocVisType = 2 AND
    ABFPosEPreis > 0 AND
    ABFPosType IN (0, 7) AND
    ABFPosNr <> ''
GROUP BY ABFPosArtNr, ABFDocAuftragNr
ORDER BY ABFPosArtNr;

Wahrscheinlich wird es noch deutlicher wenn man sich meine zuvor geposteten Screenshots ansieht.
Vielen lieben Dank nochmals für Eure Mühe.

LG Harry

[Uwe Raabe]

Im Prinzip habe ich auch nichts anderes gemacht als die beiden Abfragen zu kombinieren. Die erste Abfrage mit den "bestellt"-Werten habe ich um ein Feld "geliefert" ergänzt, dass durch ein Sub-Select realisiert wird. Offenbar gibt es aber bei Access ein Problem mit der verwendeten Syntax.

[HCB]

Ja, ja Uwe Dein Code läuft, es ist derselbe wie in Abfrage 2. Dieser bringt mir korrekt die gelieferten Mengen. Aber damit kann ich nicht die ursprünglich im Auftrag bestellte Menge sehen, sondern nur die bereits gelieferten.

LG Harry

[Uwe Raabe]

Das war mein erster Code. Der zweite ist der mit dem Subselect. Hier nochmal in abgewandelter Form mit vollständigen Alias:

SQL-Code:

SELECT
    Dok.ABFDocAuftragNr,
    Dok.ABFDocDatum,
    Dok.ABFDocKundeKurzbez,
    Dok.ABFDocSummeNetto_0,
    Pos.ABFPosMenge as bestellt,
    Pos.ABFPosArtNr,
    Pos.ABFPosName1,
    (SELECT
       SUM(subPos.ABFPosMenge)
     FROM
       ABFDok subDok
     INNER JOIN
       ABFPos subPos ON subDok.ABFDocID = subPos.ABFPosdocID
     WHERE
       subDok.ABFDocAuftragNr = Dok.ABFDocAuftragNr AND
       subPos.ABFPosArtNr = Pos.ABFPosArtNr AND
       subDok.ABFDocOptFertig = false AND
       subDok.ABFDocVisType = 2 AND
       subPos.ABFPosEPreis > 0 AND
       subPos.ABFPosType IN (0, 7) AND
       subPos.ABFPosNr <> ''
    ) AS geliefert
FROM
    ABFDok Dok
INNER JOIN
    ABFPos Pos ON Dok.ABFDocAuftragID = Pos.ABFPosDocID
WHERE
    Dok.ABFDocOptFertig = false AND
    Dok.ABFDocVisType = 1 AND
    Pos.ABFPosEPreis > 0 AND
    Pos.ABFPosType IN (0, 7) AND
    Pos.ABFPosNr <> ''
order by Dok.ABFDocAuftragNr;

[Delphi.Narium]

Klappt das?

SQL-Code:

select
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    Sum(Geliefert) as Geliefert,
    Sum(Bestellt) as Bestellt,
    ABFPosArtNr,
    ABFPosName1
from
(
  SELECT
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez,
      ABFDocSummeNetto_0,
      0 as Geliefert,
      ABFPosMenge as Bestellt,
      ABFPosArtNr,
      ABFPosName1
  FROM
      ABFDok
  INNER JOIN
      ABFPos ON ABFDok.ABFDocAuftragID = ABFPos.ABFPosDocID
  WHERE
      ABFDocOptFertig = false AND
      ABFDocVisType = 1 AND
      ABFPosEPreis > 0 AND
      ABFPosType IN (0, 7) AND
      ABFPosNr <> ''
  union all
  SELECT
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez,
      ABFDocSummeNetto_0,
      0 as Geliefert,
      Sum(ABFPosMenge) as Bestellt,
      ABFPosArtNr,
      ABFPosName1
  FROM
      ABFDok
  INNER JOIN
      ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
  WHERE
      ABFDocOptFertig = false AND
      ABFDocVisType = 2 AND
      ABFPosEPreis > 0 AND
      ABFPosType IN (0, 7) AND
      ABFPosNr <> ''
  GROUP BY 
    ABFPosArtNr, 
    ABFDocAuftragNr
) a
group by
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    ABFPosArtNr,
    ABFPosName1
ORDER BY 
  ABFPosArtNr;

[HCB]

Donnerwetter! Uwe ich liebe Dich
Du bist ein SQL Guru.
Vielen lieben Dank, du hast meinen Tag verschönert.
Dein SQL läuft perfekt so wie ich es mir vorgestellt - aber mangels Intelligenz nicht hin bekommen habe!

LG Harry

@Delphi.Narium
Dein Code liefert folgenden Fehler:
Von Alias 'Geliefert' verursachter Zirkelbezug in der SELECT-Liste der Abfragedefinition
Trotzdem Danke für Deine Unterstüzung.

[Delphi.Narium]

Auch wenn's schon 'ne Lösung gibt.

Klappt das?

SQL-Code:

select
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    Sum(a_Geliefert) as Geliefert,
    Sum(a_Bestellt) as Bestellt,
    ABFPosArtNr,
    ABFPosName1
from
(
  SELECT
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez,
      ABFDocSummeNetto_0,
      0 as a_Geliefert,
      ABFPosMenge as a_Bestellt,
      ABFPosArtNr,
      ABFPosName1
  FROM
      ABFDok
  INNER JOIN
      ABFPos ON ABFDok.ABFDocAuftragID = ABFPos.ABFPosDocID
  WHERE
      ABFDocOptFertig = false AND
      ABFDocVisType = 1 AND
      ABFPosEPreis > 0 AND
      ABFPosType IN (0, 7) AND
      ABFPosNr <> ''
  union all
  SELECT
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez,
      ABFDocSummeNetto_0,
      0 as a_Geliefert,
      Sum(ABFPosMenge) as a_Bestellt,
      ABFPosArtNr,
      ABFPosName1
  FROM
      ABFDok
  INNER JOIN
      ABFPos ON ABFDok.ABFDocID = ABFPos.ABFPosdocID
  WHERE
      ABFDocOptFertig = false AND
      ABFDocVisType = 2 AND
      ABFPosEPreis > 0 AND
      ABFPosType IN (0, 7) AND
      ABFPosNr <> ''
  GROUP BY
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez,
      ABFDocSummeNetto_0,
      ABFPosArtNr,
      ABFPosName1
) a
group by
    ABFDocAuftragNr,
    ABFDocDatum,
    ABFDocKundeKurzbez,
    ABFDocSummeNetto_0,
    ABFPosArtNr,
    ABFPosName1
ORDER BY
  ABFPosArtNr;

[HCB]

Hallo Delphi.Narium, auch diese Abfrage bringt jetzt folgenden Fehler:

<Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'ABFDocDatum' nicht als Teil der Aggregatfunktion einschließt>

LG Harry