Query zu lang (>30.000 Chars)

[former_member360849]

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!

[Former Member · Accepted solution]

Hallo,

ich würde das ganze in ein Procedure oder View/Function unterbringen. Damit würde sich im

SAP der SQL auf wenige Zeilen reduzieren.

Eine andere Möglichkeit sehe ich nicht außer den gesamten SQL-Befehl noch einmal zu überarbeiten um den ein oder anderen Befehl kürzer darstellen lassen zu können.

VG

Frohberg

[Former Member · Accepted solution]

Hi Martin,

ich würde auch den Weg über View/Views beschreiten wollen.

Wir haben für die Kalenderwoche eine hinterlegte Function (Get_ISO_Week) im SQL, das erleichtert die Abfrage dann nochmals.

Ohne Deinen Query genau zu prüfen, lese ich (für mich) heraus, dass die

OINV+INV1 (Ausgangsrechnungen)

"minus" ORIN+RIN1 (Ausgangsgutschriften)

gerechnet werden sollen (details  und Verknüpfungen zu Stammdaten mal außen vor)

Als Gedankenspiel (Query natürlich anzupassen)

Select (Kunde, Gruppe, Menge, Preis, (Menge x Preis), etc...) from OINV+INV1 (OITM, UPG1...)

UNION ALL

Select (Kunde, Gruppe, Menge, Preis, (Menge x Preis)*-1 , etc...) from ORIN+INV1 (OITM, UPG1...)

zusätzliche evtl. zu beachten:

Verkaufsanzahlungen (ODPI+DPI1)

stornierte Belege (from OINV... where OINV.Canceled = 'N')

Den Query als View gespeichert und dann per Excel über Pivot auslesen (ODBC)

Die PIVOT verdichtet die Daten und die Felder können nach Belieben versetzt und gefiltert werden.

Beispiel:

Zur Erklärung: Unser Geschäftsjahr beginnt im Oktober... daher ist KW 40 = 2016_01 (1. Monat des Geschäftsjahres 2016 = 10.2015)

Vielleicht hilft es bei Deiner Anforderung.

Gruß

Markus

[former_member360849]

Vielen Dank für die Anregungen.

Ich habe eigentlich geplant die gesamte Query zu Redesignen um es etwas zu verkürzen und ggf. noch etwas dazu zu lernen

Aus Zeitmangel habe ich einfach die Gesamte Query in eine Procedure übertragen und rufe diese im SAP mit einem simplen Procedure Call ab (EXEC ProcedureName @variables).

Hierzu habe ich noch Infos bekommen, dass mir die Stored Procedures bei einem Update von SAP beschädigt werden können oder bzw. dass diese von SAP nicht gern gesehen werden.

Ist das so?

@Markus:

Die CANCELED Belege habe ich nun auc heraus genommen, danke für die Info.

Diesen Ansatz habe ich bei einer anderen Query so durchgeführt, das funktioniert schon mal ganz gut (insbesondere da unsere Vertriebler solche Excel Fans sind).