Re: Repeat Values in query (Bug or design flaw)

Vincento Harris wrote:

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

It returns duplicate values because the joinsin the WHERE clause don't fully define the relationships between the tables. And unless one is able to analyze the data ... it is impossible to tell which table(s) and how to fix it.

Daniel Morgan

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)