Hey Gurus - can someone help me figure out why I am getting this error:
Server: Msg 16929, Level 16, State 1, Line 70
The cursor is READ ONLY.
The statement has been terminated.
Here is the query:
/*
Inactivate patients who are in Active status but only have discharged
assignments
*/
DECLARE @inactivate_DI_assign CURSOR
SET @inactivate_DI_assign = CURSOR FOR
(select * from Patient p
inner join
-- Find last discharge date
(select p.patient_id, max(pa.date_discharged) as
last_discharge
from patient p
join patient_assignment pa
on p.patient_id = pa.patient_id
and p.episode_id = pa.episode_id
/******************************************************************
The following section finds patients that have discharged assignments
but do not have active assignments
******************************************************************/
where pa.patient_id in(select patient_id from
patient_assignment where date_discharged is not null)
and pa.patient_id not in (select patient_id from
patient_assignment where date_discharged is null)
/******************************************************************/
and p.case_status = 'A'-- Only active patients
and pa.patient_id <> '00000000' -- Ignore NONCLIENT
group by p.patient_id, p.case_status
-- Only select where the last discharge was more
than 40 days ago
having (datediff(d, max(pa.date_discharged),
getdate())) > 40) z
on p.patient_id = z.patient_id
and p.case_status = 'A'-- Only active patients
and p.patient_id <> '00000000') -- Ignore NONCLIENT
OPEN @inactivate_DI_assign
FETCH NEXT FROM @inactivate_DI_assign
WHILE (@@FETCH_STATUS = 0)
BEGIN
update p
set p.case_status = 'I', -- Inactivate patient
p.case_substatus = 'DI', -- Set substatus to Discharged
p.episode_close_date = z.last_discharge, -- Set episode close = date of
last assignment discharge
p.user_id = '120DAYS', p.entry_chron = getdate(),
comments = 'Episode discharged via the 120 day rule.'-- Mark records changed
by this script
from Patient p
inner join
-- Find last discharge date
(select p.patient_id, max(pa.date_discharged) as
last_discharge
from patient p
join patient_assignment pa
on p.patient_id = pa.patient_id
and p.episode_id = pa.episode_id
/******************************************************************
The following section finds patients that have discharged episodes
but do not have active episodes
******************************************************************/
where pa.patient_id in(select patient_id from
patient_assignment where date_discharged is not null)
and pa.patient_id not in (select patient_id from
patient_assignment where date_discharged is null)
/******************************************************************/
and p.case_status = 'A'-- Only active patients
and pa.patient_id <> 00000000 -- Ignore NONCLIENT
group by p.patient_id, p.case_status
-- Only select where the last discharge was more
than 40 days ago
having (datediff(d, max(pa.date_discharged),
getdate())) > 40) z
on p.patient_id = z.patient_id
and p.case_status = 'A'-- Only active patients
and p.patient_id <> '00000000' -- Ignore NONCLIENT
WHERE CURRENT OF @inactivate_DI_assign
FETCH NEXT FROM @inactivate_DI_assign
END
CLOSE @inactivate_DI_assign
DEALLOCATE @inactivate_DI_assign
I've tried adding the FOR UPDATE clause in my set query, but then I get:
FOR UPDATE cannot be specified on a READ ONLY cursor
I have another query that uses the same format (only does not use an inner
join) and it is working fine. I don't get it.
Thanks for your help,
Craig Comperatore, CNE, MCP, CCNA, A+, Net+
IT Site Coordinator, Baycare - the Harbor BHCI
Phone: (727) 841-4200 x 248
Fax: (727) 841-4203
Confidential: This electronic message and all contents contain information
from BayCare Health System which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the addressee
only. If you are not the addressee, any disclosure, copy, distribution or use
of the contents of this message is prohibited. If you have received this
electronic message in error, please notify the sender and destroy the original
message and all copies.
MS Sql Server LazyDBA home page