Hallo zusammen,
das ist mein erster Post in in einem SAP Forum, also bitte ich milde walten zu lassen 😊
Ich habe an diversen Stellen das Problem, dass meine Querys, welche ich im SSMS erstelle einfach zu groß sind.
Im SAP B1 Abfragetool sind "nur" 30.000 Zeichen zugelassen.
Zur verdeutlichung meines Problemes hier mal ein Beispiel (nur in Ansätzen, sonst sprenge ich hier den Rahmen):
SET DATEFIRST 1
/*SELECT FROM [dbo].[OFPR] T0*/
DECLARE @Year VARCHAR(4)
/* WHERE */
--SET @Year = /* T0.Category */ '[%PrevYear]'
SET @Year = 2016
/*SELECT FROM [dbo].[OCRG] T1*/
DECLARE @group VARCHAR(20)
/* WHERE */
--SET @group = /* T1.GroupName */ '[%Group]'
SET @group = 'LIDL'
SELECT
T0.CardCode,
T3.ItemCode,
T3.ItemName,
((
SELECT
ISNULL((SUM(X1.InvQty)/*T4.AltQty*/),0)
FROM
OINV X0 INNER JOIN
INV1 X1 ON X0.DocEntry = X1.DocEntry
WHERE
X0.DocDate BETWEEN
DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1 AND
DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4) + 7 AND
X1.ItemCode = T3.ItemCode AND
X0.CardCode = T0.CardCode
)-(
SELECT
ISNULL((SUM(X1.InvQty)/*T4.AltQty*/),0)
FROM
ORIN X0 INNER JOIN
RIN1 X1 ON X0.DocEntry = X1.DocEntry
WHERE
X0.DocDate BETWEEN
DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1 AND
DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 1, DATEADD (YEAR, @Year-1900, 0)) - 4) + 7 AND
X1.ItemCode = T3.ItemCode AND
X0.CardCode = T0.CardCode
)) AS 'KW01',
((
SELECT
ISNULL((SUM(X1.InvQty)/*T4.AltQty*/),0)
FROM
OINV X0 INNER JOIN
INV1 X1 ON X0.DocEntry = X1.DocEntry
WHERE
X0.DocDate BETWEEN
DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1 AND
DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4) + 7 AND
X1.ItemCode = T3.ItemCode AND
X0.CardCode = T0.CardCode
)-(
SELECT
ISNULL((SUM(X1.InvQty)/*T4.AltQty*/),0)
FROM
ORIN X0 INNER JOIN
RIN1 X1 ON X0.DocEntry = X1.DocEntry
WHERE
X0.DocDate BETWEEN
DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1 AND
DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, 2, DATEADD (YEAR, @Year-1900, 0)) - 4) + 7 AND
X1.ItemCode = T3.ItemCode AND
X0.CardCode = T0.CardCode
)) AS 'KW02' /*etc etc bis KW53*/
FROM
OSCN T0 INNER JOIN
OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN
OCRG T2 ON T1.GroupCode = T2.GroupCode INNER JOIN
OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN
UGP1 T4 ON T3.UgpEntry = T4.UgpEntry INNER JOIN
INV1 T5 ON T3.ItemCode = T5.ItemCode INNER JOIN
OINV T6 ON T5.DocEntry = T6.DocEntry AND T0.CardCode = T6.CardCode
WHERE
T2.GroupName = @group AND
T6.DocDate BETWEEN CONCAT(@Year,'0101') AND CONCAT(@Year,'1231') AND
T4.UomEntry = 1
GROUP BY
T0.CardCode,
T3.ItemCode,
T3.ItemName,
T4.AltQty
ORDER BY
T0.CardCode,
T3.ItemCode
Hierbei führe ich die Subquery mit dem jeweiligen Alias bis zur KW 53 fort.
Diese Query bildet alle Absätze pro GP einer GP Gruppe nach KW ab (warum auch immer man so eine riesen Tapete von Abfrage haben möchte...).
Die Gesamte Query beläuft sich auf ca 57.000 Zeichen.
Das ganze habe ich bereits in einen SQL- Minimizer Tool konvertieren lassen, bin hierbei aber auch nicht unter die magische Marke der 30k Chars gekommen.
Das ganze habe ich auch schon einmal in eine Loop gepackt, bin auf eine ähnliches annehmbares Ergebnis gekommen,
habe aber eine Abfragezeit von 6,5 Minuten ðŸ˜
Hier mal die Loop:
/* @@TimeOut=[3000] */
SET DATEFIRST 1
CREATE TABLE #tempGPGroupSales(
CardCode VARCHAR(15),
ItemCode VARCHAR(20),
ItemName VARCHAR(100),
Absatz INT,
KW INT
)
/*SELECT FROM [dbo].[OFPR] T0*/
DECLARE @Year VARCHAR(4)
/* WHERE */
--SET @Year = /* T0.Category */ '[%PrevYear]'
SET @Year = 2016
/*SELECT FROM [dbo].[OCRG] T1*/
DECLARE @group VARCHAR(20)
/* WHERE */
--SET @group = /* T1.GroupName */ '[%Group]'
SET @group = 'LIDL'
DECLARE @site_value INT;
SET @site_value = 1;
WHILE @site_value <= 53
BEGIN
INSERT INTO #tempGPGroupSales (CardCode,ItemCode,ItemName,Absatz,KW)
SELECT
T0.CardCode,
T3.ItemCode,
T3.ItemName,
ISNULL((
SELECT
ISNULL((SUM(X1.InvQty)*T4.AltQty),0)
FROM
OINV X0 INNER JOIN
INV1 X1 ON X0.DocEntry = X1.DocEntry
WHERE
X0.DocDate BETWEEN
DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1 AND
DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4) + 7 AND
X1.ItemCode = T3.ItemCode AND
X0.CardCode = T0.CardCode
)-(
SELECT
ISNULL((SUM(X1.InvQty)*T4.AltQty),0)
FROM
ORIN X0 INNER JOIN
RIN1 X1 ON X0.DocEntry = X1.DocEntry
WHERE
X0.DocDate BETWEEN
DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4) + 1 AND
DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4 - DATEPART(dw, DATEADD (WEEK, @site_value, DATEADD (YEAR, @Year-1900, 0)) - 4) + 7 AND
X1.ItemCode = T3.ItemCode AND
X0.CardCode = T0.CardCode
),0) AS Absatz,
@site_value AS ID
FROM
OSCN T0 INNER JOIN
OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN
OCRG T2 ON T1.GroupCode = T2.GroupCode INNER JOIN
OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN
UGP1 T4 ON T3.UgpEntry = T4.UgpEntry INNER JOIN
INV1 T5 ON T3.ItemCode = T5.ItemCode INNER JOIN
OINV T6 ON T5.DocEntry = T6.DocEntry AND T0.CardCode = T6.CardCode
WHERE
T2.GroupName = @group AND
T6.DocDate BETWEEN CONCAT(@Year,'0101') AND CONCAT(@Year,'1231') AND
T4.UomEntry = 1
GROUP BY
T0.CardCode,
T3.ItemCode,
T3.ItemName,
T4.AltQty
ORDER BY
T0.CardCode,
T3.ItemCode
SET @site_value = @site_value+1
END
SELECT * FROM #tempGPGroupSales
DROP TABLE #tempGPGroupSales
Dynamic SQL war auch bereits schon eine Überlegung, aber hierbei sind die Abfragezeiten nach meiner Erfahrung ebenfalls im Keller.
Ich komme an dieser Stelle leider nicht weiter und hoffe hier Hilfe zu finden.
Danke und
Grüße!