Extraneous Rows Inserted in SQR

["Gordon, Darrell R." <Darrell.R.Gordon@DISNEY.COM>]

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."