Skip to Content
0
Jul 28, 2016 at 04:09 PM

Query zu lang (>30.000 Chars)

35 Views

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!