none
Cursor Als View erstellen RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    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

  • Question
    Anmelden
    1
    Anmelden

    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
    |
  • Question
    Anmelden
    1
    Anmelden

    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
    |
  • Question
    Anmelden
    1
    Anmelden

    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

  • Question
    Anmelden
    1
    Anmelden

    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
    |
  • Question
    Anmelden
    1
    Anmelden

    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
    |
  • Question
    Anmelden
    1
    Anmelden

    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
  • Question
    Anmelden
    0
    Anmelden

    Hallo,

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

    Sonntag, 15. Juli 2012 20:19
    |