Ich bin leider Anfänger in SQL. Ich habe folgende Aufgabe:
Anforderung ist folgedne:
Ich habe Query gebaut, aber habe ich Fehlermeldung: "SQL-Fehler [21000]: [SQL0811] Ergebnis der Anweisung enthält mehr als eine Zeile."
T01.OLPRDC = Articelnummer,
T04.OHODAT = Datum.
#############################
SELECT
T01.OLPRDC,
sum(T01.OLITET) AS "Umsatz Aktion",
SUM(T01.OLOQTY) AS "Menge Aktion",
(
SELECT
sum(T01.OLITET) AS "Umsatz Vergleich"
FROM
FRI510AFWF.SROORSPL T01
JOIN FRI510AFWF.SRONAM T02 ON
T01.OLCUNO = T02.NANUM
JOIN FRI510AFWF.SRBSOH T04 ON
T01.OLORNO = T04.OHORNO
JOIN FRI510AF.SROPRG T06 ON
T01.OLPRDC = T06.PGPRDC
JOIN FRI510AF.SRBCTLD1 T03 ON
T01.OLORDT = T03.CTOTYP
WHERE
T04.OHODAT BETWEEN '20230305' AND '20230405' --Vergleichzeitraum
AND T01.OLSTAT <> 'D'
AND T01.OLIORD = 'N'
AND T01.OLORDT NOT IN ('IV', 'IS', 'BL', 'IG', 'V4', 'IU')
AND T02.NACOUN IN ('DE')
AND T04.OHHAND <> 'PSHOP'
AND T01.OLPRDC IN('38099478', '11046624') GROUP BY T01.OLPRDC),
(
SELECT
SUM(T01.OLOQTY) AS "Menge Vergleich"
FROM
FRI510AFWF.SROORSPL T01
JOIN FRI510AFWF.SRONAM T02 ON
T01.OLCUNO = T02.NANUM
JOIN FRI510AFWF.SRBSOH T04 ON
T01.OLORNO = T04.OHORNO
JOIN FRI510AF.SROPRG T06 ON
T01.OLPRDC = T06.PGPRDC
JOIN FRI510AF.SRBCTLD1 T03 ON
T01.OLORDT = T03.CTOTYP
WHERE
T04.OHODAT BETWEEN '20230305' AND '20230405' --Vergleichzeitraum
AND T01.OLSTAT <> 'D'
AND T01.OLIORD = 'N'
AND T01.OLORDT NOT IN ('IV', 'IS', 'BL', 'IG', 'V4', 'IU')
AND T02.NACOUN IN ('DE')
AND T04.OHHAND <> 'PSHOP'
AND T01.OLPRDC IN('38099478', '11046624') GROUP BY T01.OLPRDC)
FROM
FRI510AFWF.SROORSPL T01
JOIN FRI510AFWF.SRONAM T02 ON
T01.OLCUNO = T02.NANUM
JOIN FRI510AFWF.SRBSOH T04 ON
T01.OLORNO = T04.OHORNO
JOIN FRI510AF.SROPRG T06 ON
T01.OLPRDC = T06.PGPRDC
JOIN FRI510AF.SRBCTLD1 T03 ON
T01.OLORDT = T03.CTOTYP
WHERE
T04.OHODAT BETWEEN '20230105' AND '20230405' --Aktionszeitraum
AND T01.OLSTAT <> 'D'
AND T01.OLIORD = 'N'
AND T01.OLORDT NOT IN ('IV', 'IS', 'BL', 'IG', 'V4', 'IU')
AND T02.NACOUN IN ('DE')
AND T04.OHHAND <> 'PSHOP'
AND T01.OLPRDC IN('38099478', '11046624')
GROUP BY
T01.OLPRDC;
Anforderung ist folgedne:
- Menge Aktionszeitraum (Summe verkaufte Stück pro Artikel und Land)
- Umsatz Aktionszeitraum, (Summe Umsatz pro Artikel und Land)
- Menge Vergleichszeitraum, (Summe verkaufte Stück pro Artikel und Land)
- Umsatz Vergleichszeitraum (Summe Umsatz pro Artikel und Land)
Ich habe Query gebaut, aber habe ich Fehlermeldung: "SQL-Fehler [21000]: [SQL0811] Ergebnis der Anweisung enthält mehr als eine Zeile."
T01.OLPRDC = Articelnummer,
T04.OHODAT = Datum.
#############################
SELECT
T01.OLPRDC,
sum(T01.OLITET) AS "Umsatz Aktion",
SUM(T01.OLOQTY) AS "Menge Aktion",
(
SELECT
sum(T01.OLITET) AS "Umsatz Vergleich"
FROM
FRI510AFWF.SROORSPL T01
JOIN FRI510AFWF.SRONAM T02 ON
T01.OLCUNO = T02.NANUM
JOIN FRI510AFWF.SRBSOH T04 ON
T01.OLORNO = T04.OHORNO
JOIN FRI510AF.SROPRG T06 ON
T01.OLPRDC = T06.PGPRDC
JOIN FRI510AF.SRBCTLD1 T03 ON
T01.OLORDT = T03.CTOTYP
WHERE
T04.OHODAT BETWEEN '20230305' AND '20230405' --Vergleichzeitraum
AND T01.OLSTAT <> 'D'
AND T01.OLIORD = 'N'
AND T01.OLORDT NOT IN ('IV', 'IS', 'BL', 'IG', 'V4', 'IU')
AND T02.NACOUN IN ('DE')
AND T04.OHHAND <> 'PSHOP'
AND T01.OLPRDC IN('38099478', '11046624') GROUP BY T01.OLPRDC),
(
SELECT
SUM(T01.OLOQTY) AS "Menge Vergleich"
FROM
FRI510AFWF.SROORSPL T01
JOIN FRI510AFWF.SRONAM T02 ON
T01.OLCUNO = T02.NANUM
JOIN FRI510AFWF.SRBSOH T04 ON
T01.OLORNO = T04.OHORNO
JOIN FRI510AF.SROPRG T06 ON
T01.OLPRDC = T06.PGPRDC
JOIN FRI510AF.SRBCTLD1 T03 ON
T01.OLORDT = T03.CTOTYP
WHERE
T04.OHODAT BETWEEN '20230305' AND '20230405' --Vergleichzeitraum
AND T01.OLSTAT <> 'D'
AND T01.OLIORD = 'N'
AND T01.OLORDT NOT IN ('IV', 'IS', 'BL', 'IG', 'V4', 'IU')
AND T02.NACOUN IN ('DE')
AND T04.OHHAND <> 'PSHOP'
AND T01.OLPRDC IN('38099478', '11046624') GROUP BY T01.OLPRDC)
FROM
FRI510AFWF.SROORSPL T01
JOIN FRI510AFWF.SRONAM T02 ON
T01.OLCUNO = T02.NANUM
JOIN FRI510AFWF.SRBSOH T04 ON
T01.OLORNO = T04.OHORNO
JOIN FRI510AF.SROPRG T06 ON
T01.OLPRDC = T06.PGPRDC
JOIN FRI510AF.SRBCTLD1 T03 ON
T01.OLORDT = T03.CTOTYP
WHERE
T04.OHODAT BETWEEN '20230105' AND '20230405' --Aktionszeitraum
AND T01.OLSTAT <> 'D'
AND T01.OLIORD = 'N'
AND T01.OLORDT NOT IN ('IV', 'IS', 'BL', 'IG', 'V4', 'IU')
AND T02.NACOUN IN ('DE')
AND T04.OHHAND <> 'PSHOP'
AND T01.OLPRDC IN('38099478', '11046624')
GROUP BY
T01.OLPRDC;