Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How Do I?

Re: How Do I?

From: Harvey <harveyb_at_NoSpambeliveau.ccHere>
Date: Mon, 06 Sep 2004 13:13:02 GMT
Message-ID: <413C625D.EF84F7D4@NoSpambeliveau.ccHere>


Rene Nyffenegger wrote:
>
> > Hi Rene:
> >
> > Thanks - it's close. The last part of it displays everything the way it
> > needs to be, however, it isn't actually updating the end_dt in the
> > table.
> >
> > Here's what I'm getting:
> >
> > EMPL_ID EFFECT_DT
> > NVL(NVL(END_DT,LEAD(EFFECT_DT)OVER(ORDERBYEFFECT_DT)-1),'31-DEC-2078')
> >======= ====================
> >======================================================================
> > BXXXXX 22-JUN-1998 00:00:00 30-JUN-1999 00:00:00
> > BXXXXX 01-JUL-1999 00:00:00 31-JUL-1999 00:00:00
> > BXXXXX 01-AUG-1999 00:00:00 31-JAN-2000 00:00:00
> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
> > BXXXXX 01-JAN-2002 00:00:00 15-MAR-2002 00:00:00
> > BXXXXX 04-MAR-2002 00:00:00 15-MAR-2002 00:00:00
> > BXXXXX 16-MAR-2002 00:00:00 15-DEC-2002 00:00:00
> > BXXXXX 16-DEC-2002 00:00:00 17-AUG-2003 00:00:00
> > BXXXXX 18-AUG-2003 00:00:00 15-AUG-2004 00:00:00
> > BXXXXX 16-AUG-2004 00:00:00 31-DEC-2078 00:00:00
> >
> > 11 ROWS SELECTED
> >
> > This does show that the logic is correct. The contents of the table
> > remain:
> >
> > EMPL_ID EFFECT_DT END_DT
> >======= ==================== ====================
> > BXXXXX 22-JUN-1998 00:00:00
> > BXXXXX 01-JUL-1999 00:00:00
> > BXXXXX 01-AUG-1999 00:00:00
> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
> > BXXXXX 01-JAN-2002 00:00:00 15-MAR-2002 00:00:00
> > BXXXXX 04-MAR-2002 00:00:00
> > BXXXXX 16-MAR-2002 00:00:00 15-DEC-2002 00:00:00
> > BXXXXX 16-DEC-2002 00:00:00 17-AUG-2003 00:00:00
> > BXXXXX 18-AUG-2003 00:00:00 15-AUG-2004 00:00:00
> > BXXXXX 16-AUG-2004 00:00:00 31-DEC-2078 00:00:00
> >
> > BTW, these are only three of the columns in the table - there are about
> > 50 in it actually.
> >
> > Your assistance is MUCH appreciated!
> >
> > Harvey
>
> Do you have a primary key on the table to be updated?
>
> --
> Rene Nyffenegger
> http://www.adp-gmbh.ch/

Not on the work table - I did create the work table sorted though, so the records are in the correct sequence.

Harvey Received on Mon Sep 06 2004 - 08:13:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US