Re: How Do I?

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

Go to http://tahiti.oracle.com and look up NVL and NVL2.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)