Re: How Do I?

From: Wario <sergeant.rock_at_gmail.com>
Date: 7 Sep 2004 11:42:47 -0700
Message-ID: <c75b43bb.0409071042.3c50fa89@posting.google.com>

try this

declare

    emp_prev emp%rowtype;     

    cursor emp_curr is

        select * from emp order by emp_id, effect_dt desc for update;

begin

    for emp_next in emp_curr loop

        if  emp_next.emp_id <> emp_prev.emp_id
        and emp_next.end_dt is null then
            update emp set end_dt = '31-DEC-2078'
            where current of emp_curr;
        end if;
        
        if  emp_next.emp_id = emp_prev.emp_id
        and emp_next.end_dt is null then
            update emp set end_dt = emp_prev.effect_dt - 1
            where current of emp_curr;
        end if;

        emp_prev := emp_next;

    end loop;     

    commit;
end;

Using PL/SQL to read the table by emp_id and effect date descending. Keep track of the last record read. This will enable you to use the last effect_dt to set the value of end_dt in the current record. Received on Tue Sep 07 2004 - 13:42:47 CDT