self referencing variable

From: stathead <duliwang_at_hotmail.com>
Date: Thu, 11 Sep 2008 11:06:35 -0700 (PDT)
Message-ID: <e8310248-ceef-4a8d-b793-cd4a5eab6933@z72g2000hsb.googlegroups.com>

group: i have a dataset as with three variables: ID, TDD,DS. i want to create a new variable, DSend, where DSend should = TDD+DS for the first record for an ID, and greatest(TDD,lag(DSend))+DS for all other records for an ID.
Results should look like

ID	TDD	DS	DSend
1	0	30	30
1	28	30	60
1	57	30	90
1	220	30	250
1	230	30	280
2	0	30	30
2	28	30	60
2	57	30	90
2	220	30	250
2	230	30	280
3	0	30	30
3	28	30	60
3	57	30	90
3	220	30	250
3	230	30	280

i'm looking for code to do this. i've tried two ways; in both, i create a variable,lID, which is the value of ID in the previous record.
METHOD 1:
Update T1
 set DSend = transactiondatediff+dayssupply where id != lID or lID is null ;
 Update T1
 set DSend = greatest(transactiondatediff,lag(DSend) over (order by id))+dayssupply where id = lID ;
the second update fails: RA 30483: window functions are not allowed here (at lag)
METHOD 2:
create table T3 as
 select a.*,
  case

     when id  != lID then transactiondatediff+dayssupply
     when id   = lID then (greatest(transactiondatediff,lag(DSend)
over (order by id)))+dayssupply
     end
     as DSend

  from T1 a;
this fails as well: ORA 00957: duplicate column name (at DSend) Anyone have any ideas on how to do this? thx Received on Thu Sep 11 2008 - 13:06:35 CDT