All Forums Database
idg_tddevex 18 posts Joined 10/06
05 Apr 2007
Selecting the previuos row in a table.

Hi, I have a table with following attributes:1. Acct_Num (Decimal 10,0) Not Null2. 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:Acct_Num Product_Code Start_Date Prev_Code Prev_Code_change_date--------- ------------- ----------- --------- ---------------------11111 123 30/11/2005 NULL NULL11111 345 01/09/2006 NULL NULL11111 345 01/01/2007 NULL NULL11111 123 30/01/2007 NULL NULLI have to update the new columns if there is a product code change else the values should remain as the prv code. Result expected:First Row - (Prev_Code = NULL, Prev_Code_change_date = NULL) - no prod code changeSecond Row - (Prev_Code = 123, Prev_Code_change_date = 01/09/2006) - prod code changeThird Row - (Prev_Code = 123, Prev_Code_change_date = 01/09/2006) - no change Fourth Row - (Prev_Code = 345, Prev_Code_change_date = 30/01/2007) - prod code change How shall i compare a row (considering start_date) with its previuos row?? or any other feasible solution in this case??

j355ga 100 posts Joined 12/05
06 Apr 2007

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 ) bwhere a.acct_num = b.acct_numand a.start_date = b.preceding_dateand a.product_code <> b.product_code

Jeff

You must sign in to leave a comment.