January 2, 2009 at 9:05 pm
Hi
I'm new to SS. The function below fails to compile, and returns the following error message:
Msg 444, Level 16, State 2, Procedure WKLY_SMA, Line 55
Select statements included within a function cannot return data to a client.
Issue lies with the FETCH RELATIVE -1 line. If I comment out the FETCH RELATIVE -1 line this compiles.
USE [NB_TRD_SYS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[WKLY_SMA]
(
-- Add the parameters for the function here
@TimeKey int,
@Commodity nvarchar(3),
@Period int
)
RETURNS int
AS
BEGIN
DECLARE @SMA FLOAT -- return variable
DECLARE @MA FLOAT
DECLARE @n INT
DECLARE @C_TKey int
DECLARE @C_CS nvarchar(3)
DECLARE @C_CLS FLOAT
DECLARE @C_First_TKey int
DECLARE @C_First_CS nvarchar(3)
DECLARE @C_First_CLS FLOAT
DECLARE SMA_CURSOR CURSOR SCROLL STATIC FOR -- Tried different types of cursors as the issue is with FETCH RELATIVE...
SELECT
TIME_KEY,
COMM_SYMB,
CLS
FROM dbo.fct_WKLY
WHERE
COMM_SYMB = @Commodity
AND
TIME_KEY <= @TimeKey
OPEN SMA_CURSOR
FETCH FIRST FROM SMA_CURSOR INTO @C_First_TKey, @C_First_CS, @C_First_CLS
FETCH LAST FROM SMA_CURSOR INTO @C_TKey, @C_CS, @C_CLS -- How do I know that my cursor has remained here?
SET @n = 0
SET @MA = 0
SET @SMA = 0
WHILE @n < @Period BEGIN
IF (@C_First_TKey = @C_TKey AND @C_First_CS = @C_CS)
RETURN @SMA
ELSE BEGIN
SET @n = @n + 1
FETCH RELATIVE -1 FROM SMA_CURSOR -- Hopefully this is fetching the previous from the LAST.
END
END
SET @SMA = @MA / @Period
CLOSE SMA_CURSOR
DEALLOCATE SMA_CURSOR
-- Return the result of the function
RETURN @SMA
END
GO
Thanks in advance,
Wayne
January 2, 2009 at 9:14 pm
Can you also post the table structure with sample data and the error you're getting.
This site should help you in posting the required solution.
www.sqlservercentral.com/articles/Best+Practices/61537/
January 3, 2009 at 12:05 am
Hello,
To quote BOL (Topic “Creating User-defined Functions“): “Only FETCH statements that assign values to local variables using the INTO clause are allowed.”
Hope that helps.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 3, 2009 at 4:09 am
Hi Waykna
There's no ORDER BY clause in the query - so FIRST and LAST will be indiscriminate. In any case, looking at the code, I'm pretty sure that this can be accomplished far more simply without the cursor.
What I suggest you do is to show a couple of examples of the data which the query returns. Include at least one dataset where IF (@C_First_TKey = @C_TKey AND @C_First_CS = @C_CS) holds true, and where it doesn't. Check the link in my sig for instructions how to prepare the data as insert statements.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 3, 2009 at 7:26 am
What's this function supposed to do?
I'd offer help in getting rid of the cursor, but I have absolutely no idea what it's doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2009 at 9:47 am
Here's a sample of the data being selected into the cursor
TIME_KEYCOMM_SYMBCLOSE
16538CL62.03
16539CL64.09
16540CL62.41
16541CL61.05
16542CL56.31
16543CL52.99
16544CL53.47
16545CL55.42
16546CL59.02
16547CL59.89
16548CL59.86
What you have is a surrogate key for a weekly time dimension, a commodity symbol for crude oil, and the weekly close. The function is supposed to calculate a running simple moving average where the window (number of periods) is parameterized. I have successfully done this is MS Access & VB. The error msg is in my first posting.
Thx,
January 3, 2009 at 9:52 am
Hi Gail
It derives a simple moving average. I have successfully implemented this in Access & VB and have been trying to accomplish the equivalent in SS.
The original VB code looks like this:
Public Function SMA_WKLY(Commodity, WkEndDt, period As Integer)
Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Double
Dim n As Integer
sql = "SELECT [COMM_SYMB], [PRD_END_DT], [CLOSE] FROM fct_WKLY" & _
" WHERE [fct_WKLY]![COMM_SYMB] ='" & Commodity & "'" & _
" AND [fct_WKLY]![PRD_END_DT] <= #" & WkEndDt & "#" & _
" ORDER BY [fct_WKLY]![PRD_END_DT];"
Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
SMA_WKLY = 0
Exit Function
Else
ma = ma + rst.Fields("CLOSE")
End If
rst.MovePrevious
Next n
rst.Close
SMA_WKLY = ma / period
End Function
What's great about this is that any number of commodities and any number of periods dynamically. As data is added to the source table, it simply picks up where you left off and derives the new simple moving averages.
Thx,
Wayne
January 3, 2009 at 10:18 am
Hi Chris
I omitted the ORDER BY, given that there's an index on the two PK fields being selected.
If this can be accomplished without a cursor that'd be great as I've read that they are a "last resort" and are generally inefficient.
I've responded to another reply by posting the entire VB function that I'm trying to reproduce - I'm migrating from MS access to SS 2008 Express.
Regarding the If statement, I'm trying to simulate the VB beginning of file (BOF)
If rst.BOF Then
SMA_WKLY = 0 (the moving average is zero)
Here's an excerpt from the table with a sample (only showing one commodity crude oil), I collect data for 50 commodities per day, with a 5-day simple moving average SMA_%
TIME_KEYCOMM_SYMBTRD_DAYOPEN CLOSESMA_5
703 CL 2006-12-0463.81 62.44 0.00
704 CL 2006-12-0562.42 62.43 0.00
705 CL 2006-12-0662.42 62.19 0.00
706 CL 2006-12-0762.31 62.49 0.00
707 CL 2006-12-0862.75 62.03 62.32
710 CL 2006-12-1162.21 61.22 62.07
711 CL 2006-12-1261.3 61.02 61.79
712 CL 2006-12-1361.85 62.17 61.79
713 CL 2006-12-1462.23 63.33 61.95
714 CL 2006-12-1563.37 64.09 62.37
717 CL 2006-12-1863.62 62.79 62.68
718 CL 2006-12-1962.73 63.46 63.17
719 CL 2006-12-2063.3 63.72 63.48
720 CL 2006-12-2163.58 62.66 63.34
In access I simply pass the TIME_KEY, COMM_SYMB, and period to the function and it derives and returns the SMA for that row. I call it from a simple select statement.
Eg. SELECT fct_WKLY.TIME_KEY, fct_WKLY.COMM_SYMB, SMA_WKLY([COMM_SYMB],[TIME_KEY],5) AS DSMA_5
I can use the same function (SMA_WKLY) to derive any period of SMA.
Thx,
Wayne
January 3, 2009 at 10:41 am
So, of the rows which are returned by the query, you use only the most recent @Period to derive the moving average?
Try the following. It's untested btw...
SELECT SUM(CLS), COUNT(*), -- <-- these two output columns appear here only for testing
CASE WHEN COUNT(*) > 0
THEN SUM(CLS) / (COUNT(*)*1.00)
ELSE 0 END AS MovingAvg
FROM (
SELECT TOP (@Period) TIME_KEY, COMM_SYMB, CLS
FROM dbo.fct_WKLY
WHERE COMM_SYMB = @Commodity AND TIME_KEY <= @TimeKey
ORDER BY TIME_KEY
) d
If this works, then the result can be streamed into a variable within the outer select, and you're done.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 3, 2009 at 10:54 am
Using a function like this in SQL at all in a query could lead to it's own performance problems, but this version is much faster, and about a zillion time simpler than either the SQL Cursors or even the Access/VB versions:
CREATE FUNCTION [dbo].[WKLY_SMA](
@TimeKey int,
@Commodity nvarchar(3),
@Period int)
RETURNS Float AS--was int?
BEGIN
DECLARE @SMA FLOAT -- return variable
SELECT @SMA = Sum(CLS) / Count(CLS)
-- correctly handles missing values and the startup window
FROM dbo.fct_WKLY
WHERE COMM_SYMB = @Commodity
AND TIME_KEY BETWEEN @TimeKey-@Period+1 and @TimeKey
-- Return the result of the function
RETURN @SMA
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 10:55 am
Oops, Chris got in before me ... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 11:04 am
RBarryYoung (1/3/2009)
Oops, Chris got in before me ... 🙂
Bet yours is tested though 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 3, 2009 at 11:15 am
Chris:
Heh. Not so much, nothing to test against.
And mine is only right if TimeKey can be assumed to be sequential (that is, without unintended gaps), otherwise you need the TOP trick that yours has.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 11:17 am
waykna (1/3/2009)
I omitted the ORDER BY, given that there's an index on the two PK fields being selected.
An index is not a guarantee of the order of the data returned. You may get the data back in the expected order, you may not. If you want a specific order applied to data, you must use an ORDER BY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2009 at 4:33 pm
Many thanks folks, I'm going to set to work in a little bit - will let you know.
Wayne
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply