November 19, 2010 at 12:41 pm
DECLARE @Symbol VARCHAR(10)
DECLARE @Period INT
DECLARE @Period_sum DECIMAL(12,6)
DECLARE @anchor INT
DECLARE @moving_sum DECIMAL(12,6)
DECLARE @initial_sum DECIMAL(12,6)
DECLARE @SQL VARCHAR(1000)
SET @Symbol ='AAPL'
SET @Period = 20
SET @SQL = 'SELECT A.ID
,A.[Date]
,A.[Close]
,CAST(NULL AS DECIMAL(12,6)) AS [Ave]
,CAST(B.[Close] AS DECIMAL(12,6)) AS [Older_Close]
INTO #tempAve
FROM t1 A
LEFT JOIN t1 B
ON A.ID - '+@Period+' = B.ID'
IF OBJECT_ID('tempdb..#tempAve ') IS NOT NULL DROP TABLE #tempAve;
WITH t1 AS (
SELECT row_number() OVER (ORDER BY [Date] ASC) ID,
[Date],
[Close]
FROM dbo.DIM_Data_OHLC
WHERE Symbol = @Symbol)
EXEC(@SQL)
CREATE CLUSTERED INDEX ix_ix ON #tempAve(ID)
--Retrieve the initial sum value at row 20
SET @initial_sum = (SELECT SUM([Close]) FROM #tempAve WHERE ID <= @Period)
UPDATE t1
SET @moving_sum = CASE
WHEN ID < @Period THEN NULL
WHEN ID = @Period THEN @initial_sum
WHEN ID > @Period THEN @moving_sum + [Close] - [Older_close] END,
Ave = @moving_sum/CAST(@Period AS DECIMAL(12,6)),
@anchor = ID
FROM #tempAve t1 WITH (TABLOCKX) OPTION (MAXDOP 1)
SELECT ROW_NUMBER() OVER (ORDER BY [Date]) AS 'num',
[Date],
[Close],
[Ave]
FROM #tempAve
go
IF OBJECT_ID('tempdb..#tempAve ') IS NOT NULL DROP TABLE #tempAve;
go
Error is
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'EXEC'.
Question: Why?😉
Ref: This me try to solve the issue from this post..
November 19, 2010 at 1:36 pm
Try putting the CTE definition into the string for the dynamic sql. I'm pretty sure you can't link dynamic sql to a CTE the way you tried to.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply