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

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1063205166.117150_at_yasure>...
> 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

I agree completely.The problem was with one of the tables and putting a distinct some where in the query resolved the issue

Thanks
Vince Received on Thu Sep 11 2003 - 06:04:49 CDT