Date: Tue, 22 Apr 2008 15:15:46 -0400
Reply-To: Chris Sanders <earlinerd@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Chris Sanders <earlinerd@GMAIL.COM>
Subject: Re: Broken index
Content-Type: text/plain; charset=ISO-8859-1
Still the same thing, I'm afraid:
update uber_data as u
set table_description =
(select o.table_description from has_comments as o
where o.table_name = u.table_name and u.table_description is null
where exists (select 1 from has_comments o
where o.table_name = u.table_name
gives this error:
ERROR: Expecting page 1, got page -1 instead.
ERROR: Page validation error while writing WORK._tf0018.INDEX.
ERROR: File WORK._tf0018.INDEX is damaged. I/O processing did not complete.
I am going to have to give up on this for right now - slow and stupid seems
to be the psychological answer to this situation.
On Tue, Apr 22, 2008 at 2:15 PM, Sigurd Hermansen <HERMANS1@westat.com>
> I didn't mean to imply that a blank table_description caused the problem
> initially. I suspected that it might be an unintended result of an update.
> proc sql;
> update uber_data as u
> set table_description =
> (select comments from has_comments as o
> where o.table_name = u.table_name and table_description is null
> where exists (select 1 from has_comments
> where o.table_name = u.table_name
> The existential operator limits the scope of the update to tuples in
> uber_data that match has_comments. While the change won't necessarily solve
> the indexing problem, the query will work correctly.
> If you are operating on a RDBMS table or have indexes defined under SAS,
> you may have to rebuild the indexes. They may have become corrupted.
> Hope that you have a brighter day ahead.
> -----Original Message-----
> *From:* Chris Sanders [mailto:email@example.com]
> *Sent:* Tuesday, April 22, 2008 1:49 PM
> *To:* Sigurd Hermansen
> *Cc:* SAS-L@listserv.uga.edu
> *Subject:* Re: Broken index
> Thank you - I couldn't get your version translated to my code, but did
> check for a blank row, and son-of-a-gun there it was, blank except for the
> row ID number.
> But, sadly, even when I deleted it, I still got the same error.
> The entire error is probably something so mindless it would not occur to
> anyone here that someone would do it. I'm in a fairly obnoxious situation
> office politics-wise at the moment and I think my brain's gone on strike.
> I'll just hand update the data till things get back to normal.
> On Tue, Apr 22, 2008 at 11:20 AM, Sigurd Hermansen <HERMANS1@westat.com>
> > Chris:
> > Interesting problem ... Do you have any instances in uber_data where
> > table_description is null and the table-name does not match any table
> > name in has_comments?
> > Why do I ask? See http://www2.sas.com/proceedings/forum2008/084-2008.pdf
> > as background. The query updates table-description from uber_data with
> > comments from has_comments only if the values of table-name in the two
> > tables match. But what happens if a tuple in uber_data has a value of
> > table_name that does not match to has_comments? In that case the query
> > reduces to
> > update uber_data as u
> > set table_description = (null)
> > where table_description is null
> > The circularity in logic may create an indexing dilemma. Whatever the
> > case, adding an existential condition will at least prevent the query
> > from updating table-description to NULL where uber_data does not have a
> > table-name match in has-comments. Change the logical conditions for the
> > update to
> > ...
> > ...where o.table_description IS NULL and o.table_name=u.table_name )
> > where exists (select 1 from has_comments as u where
> > o.table_name=u.table_name)
> > ;
> > The existential operator immediately limits updates to those instances
> > where table_names match.
> > S
> > -----Original Message-----
> > From: firstname.lastname@example.org [mailto:email@example.com]
> > On Behalf Of Chris Sanders
> > Sent: Monday, April 21, 2008 11:06 PM
> > To: SAS-L@listserv.uga.edu
> > Subject: Broken index
> > Hi, all,
> > I've got a weird error message I can't get past -
> > ERROR: Expecting page 2, got page -1 instead.
> > ERROR: Page validation error while writing WORK._tf0020.INDEX.
> > ERROR: File WORK._tf0020.INDEX is damaged. I/O processing did not
> > complete
> > This first happened on an attempted update to one dataset using a
> > correlated subquery. I think I've fixed the update, and have run a
> > modify datasets that I hope deleted the indices for that library, but am
> > still getting the error.
> > This is the update:
> > proc sql;
> > update uber_data as u
> > set table_description =
> > (select comments from has_comments as o
> > where o.table_name = u.table_name)
> > where table_description is null;
> > ;
> > Any help would be appreciated.
> > Chris