In article <413A5DC0.2F6E5B96_at_NoSpambeliveau.ccHere>, Harvey wrote:
> 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
Do you have a primary key on the table to be updated?
--
Rene Nyffenegger
http://www.adp-gmbh.ch/
Received on Sun Sep 05 2004 - 17:54:20 CDT
