Cursor
-
Hi ,
Can somebody help on this.i am getting the following error.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@PolicyNumber'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'cursor'.
Server: Msg 137, Level 15, State 1, Line 23
Must declare the variable '@PolicyNumber'.
Server: Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'cursor'.
--------------------------------------------------------------------------------------------------------------------------------------
Select distinct PolicyNumber,TableCode,SeriesCode
from #tmp1
Fetch next from cursor1 into @PolicyNumber, @TableCode,@SeriesCode
declare @LossDate datetime
declare @stop bit
Declare cursor MyCursor for
select LossDate from #tmp1
order by Month_of_file Desc
set @stop = 0
Open MyCursor
Fetch next from MyCursor into @lossDate
while @@FETCH_STATUS = 0
if LossDate > '1900 ' and @stop = 0
begin
update #tmp1
set Lossdate =@lossDate
where policynumber = @PolicyNumber and TableCode = @TableCode and SeriesCode = @SeriesCode
set @stop = 1
end
fetch next
Close cursor
deallocate cursor
--------------------------------------------------------------------------------------------------------------------------------------
-
Where were these come from?
Select distinct PolicyNumber,TableCode,SeriesCode
from #tmp1
Fetch next from cursor1 into @PolicyNumber, @TableCode,@SeriesCode
Was cursor1 another cursor you defined?
The procedure complained you never declare @PolicyNumber, @TableCode and @SeriesCode.
Also at the end
Close cursor ------> it should be Close MyCursor
deallocate cursor --------> deallocate MyCursor
-
to give you a better understanding here is my script in which i want to implement cursor .The script in bold is where i want to apply cursor approach
drop table #tmp1
create table #tmp1
(PolicyNumber varchar(10) Collate SQL_Latin1_General_CP850_BIN
, TableCode varchar(3) Collate SQL_Latin1_General_CP850_BIN
, SeriesCode varchar(1) Collate SQL_Latin1_General_CP850_BIN
, IssueAge tinyint
, Gender char Collate SQL_Latin1_General_CP850_BIN
, EffectiveDate smalldatetime
, ReserveCase decimal
, ClaimNumber varchar(7) Collate SQL_Latin1_General_CP850_BIN
, LastName varchar(20) Collate SQL_Latin1_General_CP850_BIN
, FirstName varchar(15) Collate SQL_Latin1_General_CP850_BIN
, LossDate datetime
, InClaimFile bit
, Recaptured varchar(10) Collate SQL_Latin1_General_CP850_BIN
)
Insert Into #tmp1
Select PolicyNumber, TableCode, SeriesCode, IssueAge, Gender,' ' AS EffectiveDate, Sum(ReserveCase*0.8) AS ReserveCase, '' AS ClaimNumber,
'' AS LastName,'' AS FirstName ,'' AS LossDate , '' AS InClaimFile,'' AS Recaptured
From GE_Reserve
where Month_of_file = '20060601'
Group By PolicyNumber, TableCode, SeriesCode, IssueAge, Gender,Month_of_file
Update #tmp1
Set ClaimNumber = c.ClaimNumber,LastName = c.LastName,FirstName = c.FirstName,LossDate = c.LossDate,InClaimFile = 1,EffectiveDate=c.EffectiveDate
From #tmp1 r Inner Join GE_Claim c
On r.PolicyNumber = c.PolicyNumber Collate SQL_Latin1_General_CP850_BIN
And r.TableCode = c.TableCode Collate SQL_Latin1_General_CP850_BIN
And r.SeriesCode = c.SeriesCode Collate SQL_Latin1_General_CP850_BIN
And r.IssueAge = c.IssueAge Collate SQL_Latin1_General_CP850_BIN
And r.Gender = c.Gender Collate SQL_Latin1_General_CP850_BIN
Where c.LossDate in (Select Max(LossDate) from GE_Claim
Where PolicyNumber = c.PolicyNumber And TableCode = c.TableCode And SeriesCode = c.SeriesCode And IssueAge = c.IssueAge And Gender = c.Gender
and Month_of_file = '20060601'
Group By PolicyNumber, TableCode, SeriesCode, IssueAge, Gender)
UPDATE #tmp1
SET EffectiveDate = ge.EffectiveDate,
LossDate = (Select Max(LossDate) from GE_Claim
where Month_of_file < '20060601'
and PolicyNumber = ge.PolicyNumber
and SeriesCode = ge.SeriesCode
and TableCode = ge.TableCode
and IssueAge = ge.IssueAge
and Gender = ge.Gender
group by PolicyNumber, TableCode, SeriesCode, IssueAge, Gender)
FROM #tmp1 t
INNER JOIN GE_Claim ge ON t.PolicyNumber = ge.PolicyNumber
and t.SeriesCode = ge.SeriesCode
and t.TableCode = ge.TableCode
and t.IssueAge = ge.IssueAge
and t.Gender = ge.Gender
Update #tmp1
Set Recaptured = 'Recaptured'
Where LossDate > '1900-01-01 00:00:00.000' --Is Not Null
and DateAdd(yyyy, 10, EffectiveDate) < LossDate
Update #tmp1
Set Recaptured = 'Reinsured'
Where LossDate Is Not Null
And Recaptured = '' --Is Null
-
What... replace one form of RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") in the form of a correlated sub-query for another form? I don't know your data but if the following doesn't do it, please explain how many different "EffectiveDate's" you can have for a given policy and we'll give it another whirl... but don't give up an use a
cursor...UPDATE #tmp1
SET EffectDate = d.EffectiveDate,
LossDate = d.MaxLossDate
FROM #tmp1 t
(--Dervied table "d" replaces previous slow correlated subquery
Select PolicyNumber, TableCode, SeriesCode, IssueAge, Gender,
Min(EffectiveDate) AS EffectiveDate, Max(LossDate) AS MaxLossDate
from GE_Claim
where Month_of_file < '20060601'
group by PolicyNumber, TableCode, SeriesCode, IssueAge, Gender
) d
ON t.PolicyNumber = d.PolicyNumber
and t.SeriesCode = d.SeriesCode
and t.TableCode = d.TableCode
and t.IssueAge = d.IssueAge
and t.Gender = d.Gender
Strongly recommend you do something to get rid of the hard-coded date, as well...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply