|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Wednesday, March 18, 2009 8:38 PM
Points: 23,
Visits: 42
|
|
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 @MA = @MA + @C_CLS
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,264,
Visits: 1,395
|
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:37 AM
Points: 1,086,
Visits: 1,210
|
|
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
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Monday, June 22, 2009 4:48 AM
Points: 1,759,
Visits: 3,754
|
|
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
Low-hanging fruit picker
For better assistance in answering your questions, please read this.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 12,626,
Visits: 8,658
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 18, 2009 8:38 PM
Points: 23,
Visits: 42
|
|
Here's a sample of the data being selected into the cursor
TIME_KEY COMM_SYMB CLOSE
16538 CL 62.03
16539 CL 64.09
16540 CL 62.41
16541 CL 61.05
16542 CL 56.31
16543 CL 52.99
16544 CL 53.47
16545 CL 55.42
16546 CL 59.02
16547 CL 59.89
16548 CL 59.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,
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 18, 2009 8:38 PM
Points: 23,
Visits: 42
|
|
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
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, March 18, 2009 8:38 PM
Points: 23,
Visits: 42
|
|
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_KEY COMM_SYMB TRD_DAY OPEN CLOSE SMA_5
703 CL 2006-12-04 63.81 62.44 0.00
704 CL 2006-12-05 62.42 62.43 0.00
705 CL 2006-12-06 62.42 62.19 0.00
706 CL 2006-12-07 62.31 62.49 0.00
707 CL 2006-12-08 62.75 62.03 62.32
710 CL 2006-12-11 62.21 61.22 62.07
711 CL 2006-12-12 61.3 61.02 61.79
712 CL 2006-12-13 61.85 62.17 61.79
713 CL 2006-12-14 62.23 63.33 61.95
714 CL 2006-12-15 63.37 64.09 62.37
717 CL 2006-12-18 63.62 62.79 62.68
718 CL 2006-12-19 62.73 63.46 63.17
719 CL 2006-12-20 63.3 63.72 63.48
720 CL 2006-12-21 63.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
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Monday, June 22, 2009 4:48 AM
Points: 1,759,
Visits: 3,754
|
|
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.
Low-hanging fruit picker
For better assistance in answering your questions, please read this.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 7,708,
Visits: 6,917
|
|
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
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|
