Path: utzoo!mnetor!uunet!portal!cup.portal.com!truett
From: truett@cup.portal.com
Newsgroups: comp.databases
Subject: Re: Informix query
Message-ID: <2318@cup.portal.com>
Date: 6 Jan 88 08:54:56 GMT
References: <3072@ihlpf.ATT.COM> <3890001@hpcmmb.HP.COM> <3216@ihlpf.ATT.COM> <60@coot.AUSTIN.LOCKHEED.COM>
Organization: The Portal System (TM)
Lines: 35
XPortal-User-Id: 1.1001.2190


Chris@AUSTIN.LOCKHEED.COM (Chris Wood) asks if there is a general way to
sequentially process the rows of a table, with each row processed resulting
in an insertion, deletion, update, or more complex operation on the table
being processed.

The obvious problem is that the rows may be retrieved in an arbitrary order
and, even if they are retrieved in a sorted form, the insertions and deletions
may change the ordering while processing is done.  Another problem is that
rows which are inserted may be inadvertently retrieved later in the process as
if they had been originally in the table.

Perhaps I am suggesting too simple an answer, but in SQL terms the following
should generally do it:

1.  Create a view comprised of the table to be processed with a column added to
    indicate if a row has been "processed".
2.  Perform the processing on successively fetched rows from the view.  The

     WHERE clause includes a condition that the "processed" column be NULL.
3.   A deletion from either the original table or the created view will
     remove that row from any further procesing or existence.
4.   An addition or update, however, can be made via either the original table
     or the view, allowing explicit control of whether or not the modified or
     new row will appear later for processing.R
5.   When no more rows in the view have a NULL "processed" flag, the view can
     be dropped.  The modified version of the original table can now be
     committed.

I admit that the above would fail if the relational system in use does not
preserve "view concurrency", i.e., if a view does not immediately reflect
changes in its underlying entities.

Would appreciate knowing if I have missed something fundamental.

truett@cup.portal.com  (Truett Smith, Sunnyvale, CA)