nf38453
asked on
Cursor Syntax Error
I am setting the cursor from below, and I am getting the following error: "Incorrect Syntax near 'END'". Can you please tell me what I am doing wrong with the syntax of my cursor?
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
--DECLARE VARIABLES TO BE USED WITHIN THE CURSOR
DECLARE @claim_num [numeric](18, 0),
@claim_page_num [varchar](10),
@Counter [numeric] (18, 0),
@Record_Count [numeric] (18,0)
--COUNTERS ARE SET TO FORCE CURSOR INTO LOOP UNTIL ALL RECORDS ARE PROCESSED
SET @Counter = 0
SET @Record_Count = (select count(*) from #cursor_trans)
DECLARE claim_credit_cursor CURSOR
FOR SELECT * FROM #cursor_trans
OPEN claim_credit_cursor
FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num
/*
-------------------------- ---------- ---------
select *
from #br20
where claim_num = 5508064
and claim_page_num = 1
select *
from #cursor_trans
where claim_num = 5508064
and claim_page_num = 1
select *
from dia_focus_adjustment_type
where adjustment_type_num = 23
select *
from #credit_results
-------------------------- ---------- ---------- ----
*/
--INITIATES THE LOOP THE CURSOR WILL PROCESS THROUGH
WHILE @Record_Count <> @Counter
BEGIN
IF (select count(*) from #credit_results
where @claim_num = #credit_results.claim_num
and @claim_page_num = #credit_results.claim_page _num) = 0
-------------------------- ---------- ---------- ---------- -
-------------------------- ---------- ---------- ---------- -
BEGIN
INSERT INTO #credit_results
SELECT DISTINCT #br20.claim_num, #br20.claim_page_num
FROM #br20, #cursor_trans, dia_focus_adjustment_type
WHERE #br20.claim_num = #cursor_trans.claim_num
AND #br20.claim_page_num = #cursor_trans.claim_page_n um
AND (#br20.current_balance + #cursor_trans.transaction_ amt) NOT LIKE '%-%'
AND #cursor_trans.adjustment_t ype_num = dia_focus_adjustment_type. adjustment _type_num
AND substring(dia_focus_adjust ment_type. adjustment _type,1,1) NOT like '%Y%'
AND dia_focus_adjustment_type. gl_code_nu m <> '682'
AND dia_focus_adjustment_type. adjustment _type_num not in ('4','5')
--5508064
END
ELSE
-------------------------- ---------- ---------- ---------- -
-------------------------- ---------- ---------- ---------- -
IF (select count(*) from #credit_results
where @claim_num = #credit_results.claim_num
and @claim_page_num = #credit_results.claim_page _num) = 1
BEGIN
SET @Counter = @Counter + 1
END
-------------------------- ---------- ---------- ---------- -
-------------------------- ---------- ---------- ---------- -
FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num
END
-------------------------- ---------- ---------- ---------- -
-------------------------- ---------- ---------- ---------- -
--CLOSE CURSOR RUN
CLOSE claim_credit_cursor
DEALLOCATE claim_credit_cursor
END
--------------------------
--------------------------
--DECLARE VARIABLES TO BE USED WITHIN THE CURSOR
DECLARE @claim_num [numeric](18, 0),
@claim_page_num [varchar](10),
@Counter [numeric] (18, 0),
@Record_Count [numeric] (18,0)
--COUNTERS ARE SET TO FORCE CURSOR INTO LOOP UNTIL ALL RECORDS ARE PROCESSED
SET @Counter = 0
SET @Record_Count = (select count(*) from #cursor_trans)
DECLARE claim_credit_cursor CURSOR
FOR SELECT * FROM #cursor_trans
OPEN claim_credit_cursor
FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num
/*
--------------------------
select *
from #br20
where claim_num = 5508064
and claim_page_num = 1
select *
from #cursor_trans
where claim_num = 5508064
and claim_page_num = 1
select *
from dia_focus_adjustment_type
where adjustment_type_num = 23
select *
from #credit_results
--------------------------
*/
--INITIATES THE LOOP THE CURSOR WILL PROCESS THROUGH
WHILE @Record_Count <> @Counter
BEGIN
IF (select count(*) from #credit_results
where @claim_num = #credit_results.claim_num
and @claim_page_num = #credit_results.claim_page
--------------------------
--------------------------
BEGIN
INSERT INTO #credit_results
SELECT DISTINCT #br20.claim_num, #br20.claim_page_num
FROM #br20, #cursor_trans, dia_focus_adjustment_type
WHERE #br20.claim_num = #cursor_trans.claim_num
AND #br20.claim_page_num = #cursor_trans.claim_page_n
AND (#br20.current_balance + #cursor_trans.transaction_
AND #cursor_trans.adjustment_t
AND substring(dia_focus_adjust
AND dia_focus_adjustment_type.
AND dia_focus_adjustment_type.
--5508064
END
ELSE
--------------------------
--------------------------
IF (select count(*) from #credit_results
where @claim_num = #credit_results.claim_num
and @claim_page_num = #credit_results.claim_page
BEGIN
SET @Counter = @Counter + 1
END
--------------------------
--------------------------
FETCH NEXT FROM claim_credit_cursor
INTO @claim_num, @claim_page_num
END
--------------------------
--------------------------
--CLOSE CURSOR RUN
CLOSE claim_credit_cursor
DEALLOCATE claim_credit_cursor
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.