Re: self referencing variable

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 11 Sep 2008 12:58:14 -0700
Message-ID: <1221163094.210441@bubbleator.drizzle.com>


stathead wrote:
> 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

The first thing I would suggest is that you not use reserved words for column names or variables.

Check gv$reserved_words. "ID" is a really bad idea.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Sep 11 2008 - 14:58:14 CDT

Original text of this message