none
Cursor Als View erstellen RRS feed

  • Frage

  • Hallo,

    ich hoffe Ihr könnt mir mit euren geballten Fachwissen weiterhelfen. Ich habe folgendes Problem. Ich möchte in einer View die letzten drei Rechnungszeilen pro Artikel und pro Kunde darstellen. Ich habe dies schonmal mittels Cursor umgesetzt. Hier der entsprechende Code:

    CREATE TABLE #TEMP ( [Belegnummer] int, [Zeilennummer] int, [Kundennummer] int, [Artikelnummer] int ); DECLARE @Artikel varchar(20) DECLARE @Kunde varchar(20) DECLARE Kunde CURSOR FOR SELECT [ID] FROM [dbo].[Kundenzabelle]; OPEN Kunde; FETCH NEXT FROM Kunde INTO @Kunde; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE Artikel CURSOR FOR SELECT [ID] FROM [dbo].[Artikeltabelle]; OPEN Artikel; FETCH NEXT FROM Artikel INTO @Artikel; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP SELECT TOP 3 [Rechnungsnummer], [Zeilennummer], [KundenID], [ArtikelID] FROM [dbo].[Rechnungszeilen] WHERE [KundenID] = @Kunde AND [ArtikelID] = @Artikel ORDER BY [Buchungsdatum] FETCH NEXT FROM Artikel INTO @Artikel; END CLOSE Artikel; DEALLOCATE Artikel; FETCH NEXT FROM Kunde INTO @Kunde; END; CLOSE Kunde; DEALLOCATE Kunde; SELECT * FROM #TEMP DROP TABLE #TEMP

    Allerdings kann man einen Cursor nicht zur Erstellung einer View nutzen. Zumindest ist mir kein Weg bekannt. Daher meine Frage, kann an den Code noch anders darstellen. Vielleicht mit Hilfe eine CTE oder ähnlichen. Vielen Dank für Eure Mühen.

    Samstag, 14. Juli 2012 21:56

Antworten

  • Hallo Gary,

    so sollte es gehen,

    Gruss Uli

    WITH CTE_Top3_artikel AS
    (
    SELECT [Rechnungsnummer], [Zeilennummer], [KundenID], [ArtikelID],
     ROW_NUMBER() OVER 
        (PARTITION BY KundenID, ArtikelID ORDER BY Buchungsdatum DESC) AS 'number'FROM [dbo].[Rechnungszeilen]
    
    )
    
    
    SELECT *
    FROM CTE_Top3_artikel WHERE number<4 
    ORDER BY 3,4
    

    • Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
    Sonntag, 15. Juli 2012 07:58
  • Hallo Gary,

    zunächst einmal, Du sortierst in Deinem Script nach Datum auf aufsteigend und erhälst Du die ersten 3 Rechnungen, nicht die letzten 3; Du müsstest absteigend (DESC) sortieren. Und Cursor sollte man immer möglichst vermeiden, wenn es geht.

    Das kann man in der Tat einfach mit einer CTE und der ROW_NUMBER() Funktion erledigt. Hier partitionierst Du nach Kunde und lässt nach Datum absteigend sortieren, so erhälst Du je Kunde eine fortlaufende Nummer; auf die Filterst Du dann die ersten 3 heraus.

    Am Beispiel AdventureWorsk Sales sieht es so aus.

    CREATE VIEW dbo.Last3Sales
    AS
    	WITH cust AS
    		(SELECT AccountNumber
    			   ,OrderDate
    			   ,ROW_NUMBER() OVER (PARTITION  BY AccountNumber
    								   ORDER BY OrderDate DESC
    										   ,SalesOrderID DESC) AS RowNum
    		 FROM Sales.SalesOrderHeader)
    	SELECT RowNum, AccountNumber
    	FROM cust
    	WHERE RowNum <= 3;


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    • Bearbeitet Olaf HelperMVP Sonntag, 15. Juli 2012 08:01
    • Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
    Sonntag, 15. Juli 2012 08:00
  • Hallo,

    das lässt sich mit einer Abfrage erledigen. Eins zu eins wäre z. B.:

    SELECT 
        rz.Rechnungsnummer, rz.Zeilennummer, ka.KundenID, ka.ArtikelID
    FROM (SELECT 
            k.ID AS KundenID, a.ID AS ArtikelID
        FROM dbo.Kundenzabelle AS k
        CROSS JOIN dbo.Artikeltabelle AS a) AS ka
    CROSS APPLY(SELECT TOP(3)
            r.Rechnungsnummer, r.Zeilennummer
        FROM [dbo].[Rechnungszeilen]
        WHERE r.KundenID = ka.KundenID
            AND r.ArtikelID = ka.ArtikelID
        ORDER BY Buchungsdatum) AS rz
    

    Siehe Verwenden von APPLY falls Dir das Konstrukt noch nicht bekannt sein sollte.

    Allerdings würde ich das ebensowenig wie Deinen Cursor auf eine größere Rechungstabelle loslassen.
    Der CROSS JOIN - auch durch den Cursor implementiert -  ist eine absolute Spaßbremse.

    Wenn es zu Deinen Rechnungszeilen Rechnungsköpfe oder ähnliches gibt, wie zu vermuten,
    so sollte man diese als Basis für die Abfrage verwenden.
    Das CROSS APPLY hätte damit eine reduzierte Ausgangsbasis,
    und der SQL Server mit Glück ;-) einige Indizes, die nutzen kann.

    Und wenn die spätere Verwendung nicht darin besteht, alle Zeilen aufzulisten,
    sondern nur einzelne Kunden bzw. Artikel, so könnte eine Tabellenwertfunktion besser als eine Sicht sein.

    Gruß Elmar


    • Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
    Sonntag, 15. Juli 2012 08:12
    Beantworter

Alle Antworten

  • Hallo Gary,

    so sollte es gehen,

    Gruss Uli

    WITH CTE_Top3_artikel AS
    (
    SELECT [Rechnungsnummer], [Zeilennummer], [KundenID], [ArtikelID],
     ROW_NUMBER() OVER 
        (PARTITION BY KundenID, ArtikelID ORDER BY Buchungsdatum DESC) AS 'number'FROM [dbo].[Rechnungszeilen]
    
    )
    
    
    SELECT *
    FROM CTE_Top3_artikel WHERE number<4 
    ORDER BY 3,4
    

    • Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
    Sonntag, 15. Juli 2012 07:58
  • Hallo Gary,

    zunächst einmal, Du sortierst in Deinem Script nach Datum auf aufsteigend und erhälst Du die ersten 3 Rechnungen, nicht die letzten 3; Du müsstest absteigend (DESC) sortieren. Und Cursor sollte man immer möglichst vermeiden, wenn es geht.

    Das kann man in der Tat einfach mit einer CTE und der ROW_NUMBER() Funktion erledigt. Hier partitionierst Du nach Kunde und lässt nach Datum absteigend sortieren, so erhälst Du je Kunde eine fortlaufende Nummer; auf die Filterst Du dann die ersten 3 heraus.

    Am Beispiel AdventureWorsk Sales sieht es so aus.

    CREATE VIEW dbo.Last3Sales
    AS
    	WITH cust AS
    		(SELECT AccountNumber
    			   ,OrderDate
    			   ,ROW_NUMBER() OVER (PARTITION  BY AccountNumber
    								   ORDER BY OrderDate DESC
    										   ,SalesOrderID DESC) AS RowNum
    		 FROM Sales.SalesOrderHeader)
    	SELECT RowNum, AccountNumber
    	FROM cust
    	WHERE RowNum <= 3;


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing


    • Bearbeitet Olaf HelperMVP Sonntag, 15. Juli 2012 08:01
    • Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
    Sonntag, 15. Juli 2012 08:00
  • Hallo,

    das lässt sich mit einer Abfrage erledigen. Eins zu eins wäre z. B.:

    SELECT 
        rz.Rechnungsnummer, rz.Zeilennummer, ka.KundenID, ka.ArtikelID
    FROM (SELECT 
            k.ID AS KundenID, a.ID AS ArtikelID
        FROM dbo.Kundenzabelle AS k
        CROSS JOIN dbo.Artikeltabelle AS a) AS ka
    CROSS APPLY(SELECT TOP(3)
            r.Rechnungsnummer, r.Zeilennummer
        FROM [dbo].[Rechnungszeilen]
        WHERE r.KundenID = ka.KundenID
            AND r.ArtikelID = ka.ArtikelID
        ORDER BY Buchungsdatum) AS rz
    

    Siehe Verwenden von APPLY falls Dir das Konstrukt noch nicht bekannt sein sollte.

    Allerdings würde ich das ebensowenig wie Deinen Cursor auf eine größere Rechungstabelle loslassen.
    Der CROSS JOIN - auch durch den Cursor implementiert -  ist eine absolute Spaßbremse.

    Wenn es zu Deinen Rechnungszeilen Rechnungsköpfe oder ähnliches gibt, wie zu vermuten,
    so sollte man diese als Basis für die Abfrage verwenden.
    Das CROSS APPLY hätte damit eine reduzierte Ausgangsbasis,
    und der SQL Server mit Glück ;-) einige Indizes, die nutzen kann.

    Und wenn die spätere Verwendung nicht darin besteht, alle Zeilen aufzulisten,
    sondern nur einzelne Kunden bzw. Artikel, so könnte eine Tabellenwertfunktion besser als eine Sicht sein.

    Gruß Elmar


    • Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
    Sonntag, 15. Juli 2012 08:12
    Beantworter
  • Hallo,

    vielen Dank für Eure Antworten. Ihr habt mir echt weiter geholfen. Vielen Dank nochmals dafür.

    Sonntag, 15. Juli 2012 20:19