self referencing variable

#1

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

#2

stathead wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

stathead wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

stathead wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

stathead wrote:

Quote:

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

self referencing variable

comp.databases.oracle.misc comp.databases.oracle.misc

self referencing variable - 09-11-2008 , 01:06 PM

Re: self referencing variable - 09-11-2008 , 02:58 PM

Re: self referencing variable - 09-11-2008 , 02:58 PM

Re: self referencing variable - 09-11-2008 , 02:58 PM

Re: self referencing variable - 09-11-2008 , 02:58 PM