Re: How Do I?

Rene Nyffenegger wrote:
>
> In article <4139B712.68C23997_at_NoSpambeliveau.ccHere>, Harvey wrote:
> > Hi All:
> >
> > I'm working on a project where I'm building a table, pulling data from
> > others. One of the problems that I'm working out is valuing missing
> > fields for a column that is defined as not null. Here's an example:
> >
> > 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
> >
> > Where I need to get to is where the end_dt column is valued with the
> > effect_dt - 1 of the next record, and if there is no next record (i.e,
> > the 16-Aug-2004 record), then the value of end_dt is set to 31-Dec-2078.
> >
> > If it were only a few records I'd do it by hand, but in this case it's a
> > couple thousand.
> >
> > Any assistance will be GREATLY appreciated!
>
> Harvey,
>
> The following should bring you closer. Not sure if it works on 8i though.
>
> alter session set nls_date_format='dd.MON.yyyy';
>
> create table fn(
> empl_id char(6),
> effect_dt date,
> end_dt date
> );
>
> insert into fn values
> ('BXXXXX', '22-JUN-1998', null);
> insert into fn values
> ('BXXXXX', '01-JUL-1999', null);
> insert into fn values
> ('BXXXXX', '01-AUG-1999', null);
> insert into fn values
> ('BXXXXX', '01-FEB-2000', '31-DEC-2001');
> insert into fn values
> ('BXXXXX', '01-FEB-2000', '31-dec-2001');
> insert into fn values
> ('BXXXXX', '01-JAN-2002', '15-MAR-2002');
> insert into fn values
> ('BXXXXX', '04-MAR-2002', null);
> insert into fn values
> ('BXXXXX', '16-MAR-2002', '15-DEC-2002');
> insert into fn values
> ('BXXXXX', '16-DEC-2002', '17-AUG-2003');
> insert into fn values
> ('BXXXXX', '18-AUG-2003', '15-AUG-2004');
> insert into fn values
> ('BXXXXX', '16-AUG-2004', '31-DEC-2078');
>
> select
> empl_id,
> effect_dt,
> nvl(
> nvl(end_dt, lead(effect_dt) over(order by effect_dt) -1),
> '31-DEC-2078'
> )
> from fn;
>
> hth,
> Rene
>
> --
> Rene Nyffenegger
> http://www.adp-gmbh.ch/

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 Received on Sat Sep 04 2004 - 19:28:48 CDT