SQL & PL/SQL

I have a table with values similar to this:

id term val
1 200720 aaa
1 200720 bbb
1 200720 ccc
1 201220 ddd
1 201220 eee

2 200720 qqq
2 200720 rrr
2 200920 sss

What I need, is to get the value of the next greater term (999999 if non exists) for each row within id, like this:

id term val next_term
1 200720 aaa 201220
1 200720 bbb 201220
1 200720 ccc 201220
1 201220 ddd 999999
1 201220 eee 999999

2 200720 qqq 200920
2 200720 rrr 200920
2 200920 sss 999999

I tried lead term over partitiion by id, but that just gives me the value in the next row. I looked at windowing, but my eyes crossed... Any ideas? I need the next higher value not the value in the next row. The next_term value should never be the same as the term value

CREATE TABLE WJPTEST
(
ID VARCHAR2(3 CHAR),
TERM VARCHAR2(6 CHAR),
VAL VARCHAR2(3 CHAR)
)
;

Insert into WJPTEST
(ID, TERM, VAL)
Values
('1', '200720', 'aaa');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('1', '200720', 'bbb');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('1', '200720', 'ccc');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('1', '201220', 'ddd');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('1', '201220', 'eee');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('2', '200720', 'qqq');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('2', '200720', 'rrr');
Insert into WJPTEST
(ID, TERM, VAL)
Values
('2', '200920', 'sss');
COMMIT;

Edited by: wjpenfold on Jun 12, 2012 10:08 AM