Repeat Values in query (Bug or design flaw)

From: Vincento Harris <wumutek_at_yahoo.com>
Date: 10 Sep 2003 07:29:23 -0700
Message-ID: <2fa13ee7.0309100629.53fb3319@posting.google.com>

Greetings
This code was inherited and erred out each time.After some minor changes it could run but the return values were questionable. I cannot tell why this query is returning duplicate values. Will greatly appreciate your advice..
 RDBMS Oracle Version 8.1.7.4 Enterprise Edition OS Hp unix

Vince

SELECT MDJ.EMPLID, MDJ.EFFDT, MDJC.EEO1CODE

    FROM  sysadm.PS_JOB MDJ,
          sysadm.PS_JOBCODE_TBL MDJC,
          sysadm.PS_PERS_MILIT_USA MD
    WHERE MD.EMPLID = MDJ.EMPLID
      AND MD.MIL_DISCHRG_DT_USA Between '31-JUL-02' and '31-JUL-03'
      AND MDJ.EFFDT = (SELECT MAX(MDJ1.EFFDT)
                       FROM sysadm.PS_JOB MDJ1
                       WHERE MDJ1.EMPLID = MDJ.EMPLID
                        AND MDJ1.EMPL_RCD = MDJ.EMPL_RCD
                       AND MDJ1.EFFDT Between '31-JUL-02' and
'31-JUL-03'
                       AND MDJ1.ACTION IN ('HIR','REH')
                        AND MDJ1.REG_TEMP   = 'R' )
     AND MDJ.EFFSEQ = (SELECT MAX (MDJ2.EFFSEQ)
                      FROM sysadm.PS_JOB MDJ2
                      WHERE MDJ2.EMPLID = MDJ.EMPLID
                        AND MDJ2.EMPL_RCD = MDJ.EMPL_RCD
                        AND MDJ2.EFFDT = MDJ.EFFDT)

   AND MDJ.JOB_INDICATOR = 'P'

    AND MDJC.JOBCODE = MDJ.JOBCODE
    AND MDJC.SETID = MDJ.SETID_JOBCODE
    AND MDJC.EFFDT = (SELECT MAX(MDJC1.EFFDT)
                      FROM sysadm.PS_JOBCODE_TBL MDJC1
                      WHERE MDJC1.JOBCODE = MDJC.JOBCODE
                        AND MDJC1.SETID = MDJC.SETID
                        AND MDJC1.EFFDT <= MDJC.EFFDT)

    AND MDJ.EEO_CLASS <> 'E'
    AND MDJC.EEO1CODE <> 'N'
    AND MDJ.ESTABID = '500';
EMPLID EFFDT E

----------- --------- -
011811      23-DEC-02 7
011811      23-DEC-02 7

Received on Wed Sep 10 2003 - 09:29:23 CDT