I have a table with following attributes: 1. Acct_Num (Decimal 10,0) Not Null 2. Prod_Code (Char 5) 3. Start_Date (Date format 'yyyy-mm-dd')
The index is Acct_Num & not UPI.
The table is already present with about 60 million rows & now I am adding up to new columns as Prev_Code & Prev_Code change date initially poupulating them as NULL. I have to update all the historic rows. Here is an interesting scenario:
you can try somthing along the lines of emulating the lag function. It would be more or less along the lines of this example:
select * from foo a, (select acct_num, product_code, start_date, max(start_date) over (partition by acct_num order by acct_num, start_date rows between 1 preceding and 1 preceding) as preceding_date from foo ) b where a.acct_num = b.acct_num and a.start_date = b.preceding_date and a.product_code <> b.product_code