Funktion in eine View packen

[Robin_]

Hallo Zusammen,

ich versuche seit einer Weile die folgende Aufgabe zu lösen:

gegeben ist eine Tabelle der Form

datum           nummer1    nummer2
2012-03-01        90101      90103
2012-03-02        70210      70211
2012-03-03        60650      60651

Das Ziel ist es abhängig von den Spalten nummer1 und nummer2 weitere Einträge in die Tabelle einzufügen. d.h.:

Solange nummer1 < nummer2
füge datum, nummer1, nummer2 in die Tabelle
erhöhe nummer1 um 1

Das habe ich folgendermaßen als Funktion hinbekommen:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION function_v1
(@dtm as datetime, @Param_von as float, @Param_bis as float)

RETURNS @tbl TABLE (datum Datetime, nmr1 float, nmr2 float)
AS
BEGIN
	--DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
	WHILE @Param_von < @Param_bis
		BEGIN
		SET @Param_von = @Param_von + 1
		INSERT INTO @tbl VALUES (@dtm, @Param_von, @Param_bis)
		END
	
	RETURN

END
GO

Die Frage ist, wie packe ich diese Funktion in eine View, um die gewünschte Ergebnis zu bekommen:

datum           nummer1    nummer2
2012-03-01        90101      90103
2012-03-01        90102      90103
2012-03-01        90103      90103
2012-03-02        70210      70211
2012-03-02        70211      70211
2012-03-03        60650      60651
2012-03-03        60651      60651

Die View, die ich probiert habe, ist die folgende:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Auswertung_v3]
AS
SELECT      datum, nmr1, nmr2
FROM         dbo.function_v1(dbo.tbl.Datum, dbo.tbl.OpNr1, dbo.tbl.OpNr2)

Funktioniert aber nicht. Ich gehen davon aus, dass der Fehler etwas mit den Funktionsparametern zu tun hat.

Hat jemand eine Idee ?

Danke im Voraus

[ebis]

APPLY verwenden

[Thomas Henkel]

ok, hier noch mal als Antwort:

ich würde eine View mit Rekursion verwenden:

SELECT * from Test;
datum        | nr1   | nr2
'2012-03-01' | 90101 | 90103
'2012-03-02' | 70210 | 70211
'2012-03-03' | 60650 | 60651

with tblData (datum, nr1, nr2, anz) AS
(
-- anchor
		select datum, nr1, nr2, 0 as anz from Test
		union all
-- recursive definition
		select t1.datum, t2.nr1+1, t1.nr2, anz+1
		from Test t1 join tblData t2 on t1.datum=t2.datum and t2.nr1<t2.nr2
)
select datum, nr1, nr2
from tblData
order by datum, nr1;

Thomas

[Robin_]

Danke für eure Unterstützung

@ebis Dein Hinweis hat mich einen Schritt weitergebracht.

Das Problem ist, dass man APPLY nur dann verwenden kann, wenn der Kompatibilitätsgrad der Datenbank mindestens 90 (SS 2005) ist.
Die von mir benutzte Datenbank hat einen Kompatibilitätsgrad von 80, obwohl mein SQL Server Version 2008 ist. Und es gibt keine Möglichkeit die Kompatibilität zu erhöhen.

Eigenschaften -> Optionen : man kann nur zwischen 70 und 80 auswählen!

Die Frage ist wie setze ich die Datenbank auf 90 oder 100 ?

@ Thomas Deine Idee mit der Rekursion ist super
aber

[Robin_]

......

@ Thomas Deine Idee mit der Rekursion ist super
aber es tritt die folgende Fehlermeldung auf:

Incorrect syntax near the keyword 'WITH'.

Ich gehe davon aus, dass dies auch mit der Kompatibilität zusammenhängt.

[Thomas Henkel]

In der Tat habe ich hier 2008, aber WITH sollte schon in 2005 funktionieren. Was hast du denn genau geschrieben?

http://blog.jerrynixon.com/2008/03/c...-sql-2005.html

Thomas

[ebis]

In der Tat habe ich hier 2008, aber WITH sollte schon in 2005 funktionieren.

Wenn, wie oben beschrieben der Kompatibilitätsgrad 80 ist, also SQL 2000, dann geht auch kein WITH...

[ebis]

Die Frage ist wie setze ich die Datenbank auf 90 oder 100?

[HIGHLIGHT="SQL"]ALTER Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 90
ALTER Database SET Multi_User[/HIGHLIGHT]
Würde aber vorher ein Backup machen

Weiteres: Compability

[Robin_]

Hm, Ich hab mich geirrt! Der von mir eingesetzte SQL Server ist SS 2000.
Daher ist das Thema Kompatibilät vom Tisch.

Kann man die obengenannte Aufgabe trotzdem lösen, indem man andere Konstrukte verwendet oder muss man die Datenbank
auf SS 2005 oder 2008 migrieren ?

[Thomas Henkel]

Schau mal hier Recursive queries with SQL Server 2000. Der Artikel verlinkt mehrere Möglichkeiten, damit kann man die Rekurison vielleicht umschreiben.

Thomas

[Robin_]

Hallo Zusammen,

ich habe für meine Testumgebung den SS 2008 installiert. Der Code mit der Rekursion von @Thomas funktioniert einwandfrei.

Den Hinweis von @ebis (UDF mit CROSS APPLY) wollte ich aber auch testen. Das Ergebnis ist jedoch falsch:

[highlight=sql]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.udf
(
@dtm as datetime,
@prm_von as float,
@prm_bis as float
)
RETURNS @tblData TABLE
(datum datetime, nr1 float, nr2 float)
AS
BEGIN
-- Fill the table variable with the rows for your result set
WHILE @prm_von <= @prm_bis
BEGIN
SET @prm_von = @prm_von + 1
INSERT INTO @tblData VALUES (@dtm,@prm_von,@prm_bis)
END

RETURN
END
GO

SELECT a.datum, a.nr1, a.nr2
FROM dbo.tbl a
CROSS APPLY dbo.udf(a.datum,a.nr1,a.nr2) as st

GO
[/highlight]
Das Resultat sieht wie folgt aus:

datum                    nr1     nr2
2012-03-01 10:00:00	90101	90103
2012-03-01 10:00:00	90101	90103
2012-03-01 10:00:00	90101	90103
2012-03-02 10:00:00	70210	70212
2012-03-02 10:00:00	70210	70212
2012-03-02 10:00:00	70210	70212
2012-03-03 10:00:00	60650	60653
2012-03-03 10:00:00	60650	60653
2012-03-03 10:00:00	60650	60653
2012-03-03 10:00:00	60650	60653

Gewünscht ist aber:

datum                    nr1     nr2
2012-03-01 10:00:00	90101	90103
2012-03-01 10:00:00	90102	90103
2012-03-01 10:00:00	90103	90103
2012-03-02 10:00:00	70210	70212
2012-03-02 10:00:00	70211	70212
2012-03-02 10:00:00	70212	70212
2012-03-03 10:00:00	60650	60653
2012-03-03 10:00:00	60651	60653
2012-03-03 10:00:00	60652	60653
2012-03-03 10:00:00	60653	60653

Ich verstehe nicht warum sich @prm_von nicht inkrementieren lässt ?

Danke im Voraus
Robin

[ebis]

Ich verstehe nicht warum sich @prm_von nicht inkrementieren lässt ?

Wie wärs mit Selektieren der durch die Funktion berechneten Werte und nicht der Daten aus der Tabelle, letztere verändern sich schließlich nicht...
[HIGHLIGHT="SQL"]SELECT a.datum, st.nr1, st.nr2
FROM dbo.tbl a
CROSS APPLY dbo.udf(a.datum,a.nr1,a.nr2) AS st[/HIGHLIGHT]

[ebis]

ab 2005 geht das auch ohne eigene Funktion:
[HIGHLIGHT="SQL"]WITH Data (Date, Von , Bis) AS
( SELECT '2012-03-01 10:00:00', 90101, 90103 UNION ALL
SELECT '2012-03-01 10:00:00', 90101, 90103 UNION ALL
SELECT '2012-03-01 10:00:00', 90101, 90103 UNION ALL
SELECT '2012-03-02 10:00:00', 70210, 70212 UNION ALL
SELECT '2012-03-02 10:00:00', 70210, 70212 UNION ALL
SELECT '2012-03-02 10:00:00', 70210, 70212 UNION ALL
SELECT '2012-03-03 10:00:00', 60650, 60653 UNION ALL
SELECT '2012-03-03 10:00:00', 60650, 60653 UNION ALL
SELECT '2012-03-03 10:00:00', 60650, 60653 UNION ALL
SELECT '2012-03-03 10:00:00', 60650, 60653
)
SELECT Date
, ROW_NUMBER () OVER (PARTITION BY Bis ORDER BY Date) + Von - 1 AS Von
, Bis
FROM Data
ORDER BY Date, Von[/HIGHLIGHT]
[HIGHLIGHT="Ergebnis"]Date Von Bis
------------------- -------------------- -----------
2012-03-01 10:00:00 90101 90103
2012-03-01 10:00:00 90102 90103
2012-03-01 10:00:00 90103 90103
2012-03-02 10:00:00 70210 70212
2012-03-02 10:00:00 70211 70212
2012-03-02 10:00:00 70212 70212
2012-03-03 10:00:00 60650 60653
2012-03-03 10:00:00 60651 60653
2012-03-03 10:00:00 60652 60653
2012-03-03 10:00:00 60653 60653[/HIGHLIGHT]