Re: Extraneous Rows Inserted in SQR

[Arjan Hoornstra <ahoor@YAHOO.COM>]

I have had a similar problem with the update of a row selected by SQR.

I can describe the problem which applies for Sybase, don't know how
much applies for or can be translated to Informix.

The problem is that, if you insert an extra row, it will go to a
datapage. The sqr-select creates a cursor on the table. Now if you add
a row to the bottom of the table (depending on index) it will be
selected in the end by the sqr-cursor. If you insert it at the head of
the table, the cursor has passed this page already so it will not be
selected again. Then, if you do an insert, the page on which the row is
inserted may be split because the extra row must fit between 2 existing
rows. This results in a different number and order of pages which can
be selected or reselected by the cursor.

So the simple advice is: never place your sqr-cursor on the table you
wish to insert or update, or never do an insert or update on the table
your sqr-cursor is placed on.

You should either:
- place the table you need in a temporary table where you put the
cursor on, insert/update the original
- or store your inserts/updates in a temporary table which you all
execute after the cursor is finished.

Hope this helps...
Arjan



--- "Gordon, Darrell R." <Darrell.R.Gordon@DISNEY.COM> wrote:
> Hello all -
>
>  We are running PeopleSoft HR 7.5, People Tools 7.58 - with Informix
> 9.21HC5, and Unix 11.0   I am having a problem with an SQR that is
> selecting
> rows that I have inserted in the program.  Here is my select:
>
>                 FROM PS_JOB J,
>              PS_PERSONAL_DATA PD,
>              PS_EMPLOYMENT EMP
>         WHERE J.EFFDT  = (SELECT MAX(J1.EFFDT)
>                     FROM PS_JOB J1
>                    WHERE J1.EMPLID = J.EMPLID
>                      AND J1.EMPL_RCDN = J.EMPL_RCDN
>                      AND J1.EFFDT <= $Process_Date)
>           AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
>                     FROM PS_JOB J2
>                    WHERE J2.EMPLID = J.EMPLID
>                      AND J2.EMPL_RCDN = J.EMPL_RCDN
>                      AND J2.EFFDT = J.EFFDT)
>           AND J.EMPL_STATUS IN ('A', 'L', 'P', 'S')
>           AND J.REG_TEMP IN ('R', 'T')
>           AND J.FULL_PART_TIME IN ('F', 'P')
>           AND J.EMPL_CLASS = 'A'
>           AND J.SAL_ADMIN_PLAN = 'CMA'
>           AND PD.EMPLID = J.EMPLID
>           AND PD.PER_STATUS = 'E'
>           AND EMP.EMPLID = J.EMPLID
>           AND EMP.EMPL_RCDN = J.EMPL_RCDN
>
> In the above example, process_date is '2001-10-01'.   Based on my
> Select, I
> may insert a row with an effective date of 2001-10-01.  If one
> already
> exists, I increment the sequence # to avoid an error on a duplicate
> key.
> One of the LAST steps in the SQR is to perform the COMMIT (after
> selecting/inserting rows).
>
> The problem is my Select is arbitrarily retrieving some of the rows I
> have
> just inserted.  When it does, it inserts another row (with an
> incremented
> sequence #).  There is no rhyme or reason or pattern.  Sometimes it
> may
> insert 1 or up to 5 extraneous rows.
>
> We have a workaround - storing the EMPLID in a temporary variable
> (Temp_Emplid), adding an ORDER BY J.EMPLID and selecting where
> J.EMPLID is >
> than Temp_Emplid.
>
> Has anyone else experienced this problem ??
>
>
>
> Darrell Gordon III
> WDW Information Technologies
> tie-line:    8-264-1972
> voice:  407-560-1972
> fax:      407-560-6562
> "This communication is confidential, intended only for the named
> recipient(s) above and may contain trade secrets or other information
> that
> is exempt from disclosure under applicable law. Any use,
> dissemination,
> distribution or copying of this communication by anyone other than
> the named
> recipient(s) is strictly prohibited. If you have received this
> communication
> in error, please immediately notify us by calling (407) 560-1972.
> Thank
> you."


__________________________________________________
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone.
http://phone.yahoo.com