First cursor statement causes compile error 1

[vba317]

I am using SQL 2005. I created my first sql loop using the cursor method. Unfortunately I am getting an error message incorrect syntax near the word open. The current value for @rptpd is an integer 383. What I need the loop to do is increment 12 times . Any help in this matter is appreciated.

Tom

DECLARE @rptpd int

SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='EMA' and monasdt ='11/1/2013')
 
DECLARE pd CURSOR FOR

OPEN pd
FETCH NEXT FROM pd INTO @rptpd
WHILE @@FETCH_STATUS = 0   
 
BEGIN

SELECT 
cs.uci
,pd.mon_nm + ' ' + pd.yr as Report_Pd
,cs.aid
,p.PatName
,p.AcctNu
,Convert (varchar(10),cs.dos,101) as DOS
,cs.priminsmne + '-' + pins.insdesc as 'Primary Insurance'
,(case when cs.trantype = 1 then Sum(cs.amt) else 0 end) as Charges
,(case when cs.trantype = 3 and cs.crcat = 'CONTR_ADJ' then Sum(cs.amt) else 0 end) as 'Contractual Adjustments'
,(case when cs.trantype = 3 and cs.crcat = 'WRITE_OFF' OR cs.crcat ='ADMIN_ADJ' then Sum(cs.amt) else 0 end) as 'Write-Offs'
,(case when cs.trantype = 4 then Sum(cs.amt) else 0 end) as Receipts
,(case when cs.trantype = 2 then Sum(cs.amt) else 0 end) as Debits
FROM rpt_dat_CSDetail cs
	INNER JOIN rpt_Clients cl ON cs.clntid = cl.clntid
	INNER JOIN rptdata_ahs.dbo.dic_Period pd ON cs.rptpd = pd.pd
	INNER JOIN rptdata_ahs.dbo.bi_PatientData p ON p.clntid = cs.clntid and p.aid = cs.aid
	INNER JOIN rpt_dic_Ins pins ON cs.clntid = pins.clntid AND cs.priminsmne = pins.insmne
WHERE cs.clntid = 15 and cs.rptpd = @rptpd
GROUP BY 
cs.uci
,cs.rptpd
,pd.mon_nm
,pd.yr
,cs.aid
,p.PatName
,p.AcctNu
,cs.dos
,cs.priminsmne
,cs.trantype
,cs.crcat
,cs.amt
,pins.insdesc
ORDER BY 
cs.uci
,pd.mon_nm
,pd.yr
,cs.aid
,p.PatName
,p.AcctNu
,cs.dos
,cs.priminsmne
,cs.trantype
,cs.crcat
,pins.insdesc


END   

CLOSE pd 
DEALLOCATE pd

 

[vba317]

Sorry to misstate my original statement this is my second loop.

 

[Olaf Doschke]

The syntax to declare a cursor is DECLARE name CURSOR FOR sql-query. After that you can OPEN name and fetch.

You haven't defined a query for your pd cursor.

Bye, Olaf.

 

[vba317]

Olaf,

I added a select statement (which is in blue) that will return 12 records for each month of the year. Now I am getting an error incorrect syntax near the keyword for.

DECLARE @rptpd int

SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='EMA' and monasdt ='11/1/2013')
 
DECLARE @pd CURSOR FOR

[BLUE] SELECT rptpd
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='EMA' and fy=2014 [/BLUE]

OPEN pd
FETCH NEXT FROM pd INTO @rptpd
WHILE @@FETCH_STATUS = 0   
 
BEGIN

 

[Olaf Doschke]

DECLARE pd CURSOR FOR SELECT rptpd
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='EMA' and fy=2014

No @ for cursor name, cursors are not varibles, though they are also declared.

Bye, Olaf.

 

[vba317]

OK I changed the code. Now I am not getting any errors but the data is bad. The rptpd is staying at the first period for each loop.

DECLARE @rptpd int;

DECLARE pd CURSOR FOR


SELECT rptpd
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='EMA' and fy=2014; 

OPEN pd;
FETCH NEXT FROM pd INTO @rptpd
WHILE @@FETCH_STATUS = 0   
 
BEGIN

 

[vba317]

I realized the error of my ways. I needed an additional fetch statement at the end of my second query so the report period would increment. With the additional fetch statement the data is correct.

Thanks Olaf

ALTER PROCEDURE [dbo].[sproc_Rpts_EMA_Audit]
	
AS

/*************************************************
**************************************************/
DECLARE @rptpd int;

DECLARE pd CURSOR FOR


SELECT rptpd
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='EMA' and fy=2014; 

OPEN pd;
FETCH NEXT FROM pd INTO @rptpd
WHILE @@FETCH_STATUS = 0   
 
BEGIN

SELECT 
cs.uci
,pd.mon_nm + ' ' + pd.yr as Report_Pd
,cs.aid
,p.PatName
,p.AcctNu
,Convert (varchar(10),cs.dos,101) as DOS
,cs.priminsmne + '-' + pins.insdesc as 'Primary Insurance'
,(case when cs.trantype = 1 then Sum(cs.amt) else 0 end) as Charges
,(case when cs.trantype = 3 and cs.crcat = 'CONTR_ADJ' then Sum(cs.amt) else 0 end) as 'Contractual Adjustments'
,(case when cs.trantype = 3 and cs.crcat = 'WRITE_OFF' OR cs.crcat ='ADMIN_ADJ' then Sum(cs.amt) else 0 end) as 'Write-Offs'
,(case when cs.trantype = 4 then Sum(cs.amt) else 0 end) as Receipts
,(case when cs.trantype = 2 then Sum(cs.amt) else 0 end) as Debits
FROM rpt_dat_CSDetail cs
	INNER JOIN rpt_Clients cl ON cs.clntid = cl.clntid
	INNER JOIN rptdata_ahs.dbo.dic_Period pd ON cs.rptpd = pd.pd
	INNER JOIN rptdata_ahs.dbo.bi_PatientData p ON p.clntid = cs.clntid and p.aid = cs.aid
	INNER JOIN rpt_dic_Ins pins ON cs.clntid = pins.clntid AND cs.priminsmne = pins.insmne
WHERE cs.clntid = 15 and cs.rptpd = @rptpd
GROUP BY 
cs.uci
,cs.rptpd
,pd.mon_nm
,pd.yr
,cs.aid
,p.PatName
,p.AcctNu
,cs.dos
,cs.priminsmne
,cs.trantype
,cs.crcat
,cs.amt
,pins.insdesc
ORDER BY 
cs.uci
,pd.mon_nm
,pd.yr
,cs.aid
,p.PatName
,p.AcctNu
,cs.dos
,cs.priminsmne
,cs.trantype
,cs.crcat
,pins.insdesc

FETCH NEXT FROM pd INTO @rptpd 

END   

CLOSE pd; 
DEALLOCATE pd;