Thanks for the suggestion, but I still get the same error. I think my
problem involves the aggregate.
I'm going to go in a new direction. I'm going to create a custom table to
hold the last_discharge value, select into that table, & then use the cursor
to update Patient with the info from my custom table.
We'll see.
Craig
-----Original Message-----
From: Jay Grubb
[mailto:mssqldba-ezmlmshield-x13729411.[Email address protected]
Sent: Monday, February 27, 2006 1:22 PM
To: LazyDBA Discussion
Subject: RE: The cursor is READ ONLY
Just for the heck of it, quick guess:
Most Positioned updates require an unambiguous mapping to the underlying
data record. You've muddled things by using the alias p in 2 different
contexts, and you have a group by. You must be sure that:
All the "FOR UPDATE OF" columns are being mapped to the "outside" base
table, not the one in the sub select. Use a different alias
That a PK or Unique index is included.
That the Group By is not confusing things. By definition, a group by is
not mappable to base records.
Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers
-----Original Message-----
From: Comperatore Craig
[mailto:mssqldba-ezmlmshield-x1074200.[Email address protected]
Sent: Monday, February 27, 2006 1:04 PM
To: LazyDBA Discussion
Subject: RE: The cursor is READ ONLY
This gives me the error:
Server: Msg 16957, Level 16, State 4, Line 2
FOR UPDATE cannot be specified on a READ ONLY cursor.
Craig
-----Original Message-----
From: Devin Ben-Hur [mailto:mssqldba-ezmlmshield-x55932940.[Email address
protected]
Sent: Friday, February 24, 2006 12:48 PM
To: LazyDBA Discussion
Subject: Re: The cursor is READ ONLY
Comperatore Craig wrote:
> I changed my declare statement to:
You're syntax is incorrect check
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_de-dz_31yq.asp>
You need a FOR statement and have to loss the outer parens around your
select to get the syntax right. I suggest FORWARD_ONLY SCROLL_LOCKS
cursor mode options too for your usage.
Also, you may not specify any columns from alias Z in the FOR UPDATE
clause. Z's fields are derived values, you can not SET them (this was
likely the source of your earlier read_only error message). The columns
you specify in FOR UPDATE OF should be the same columns you target
(assign) with the SET clause of your UPDATE... FOR CURRENT OF.
Here's what it should look like:
DECLARE @inactivate_DI_assign CURSOR
FORWARD_ONLY SCROLL_LOCKS FOR
SELECT p.*, z.last_discharge
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
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
having datediff(d,max(pa.date_discharged),getdate()) > 40
) z
on p.patient_id = z.patient_id
where p.case_status = 'A'
and p.patient_id <> '00000000'
FOR UPDATE OF p.case_status, p.case_substatus, p.episode_close_date
,p.user_id, p.entry_chron, p.comments
--
Devin Ben-Hur | President / CTO
eMarket Group, Ltd | http://www.emarket-group.com
Zat.com | http://www.Zat.com
503/445-8028 | mailto:[Email address protected]
At least when a friend shoots you in the face,
you know where he stands.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
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.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
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