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
Comments: To: Sigurd Hermansen <HERMANS1@westat.com>
In-Reply-To:  <CA8F89971ADA9F47A6C915BA2397844207B426D5@MAILBE2.westat.com>
Content-Type: text/plain; charset=ISO-8859-1

Sigurd,

Still the same thing, I'm afraid:

This code:
proc sql;
  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
                         )
     ;
quit;

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.

Chris

On Tue, Apr 22, 2008 at 2:15 PM, Sigurd Hermansen <HERMANS1@westat.com>
wrote:

>  Chris:
> 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.
>
> Try
> 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
>                          )
>      ;
> quit;
>
> 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.
> S
>
>  -----Original Message-----
> *From:* Chris Sanders [mailto:earlinerd@gmail.com]
> *Sent:* Tuesday, April 22, 2008 1:49 PM
> *To:* Sigurd Hermansen
> *Cc:* SAS-L@listserv.uga.edu
> *Subject:* Re: Broken index
>
> Sigurd,
>
> 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.
>
> Chris
>
>
> On Tue, Apr 22, 2008 at 11:20 AM, Sigurd Hermansen <HERMANS1@westat.com>
> wrote:
>
> > 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: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
> > 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
> >
>
>