That's great Serge- it works :). But, now it's got a little more
complicated...
Imagine in the table
contract date
1. CONTRACT A 2005-12-01
2. CONTRACT B 2006-03-01
3. CONTRACT C 2006-06-01
4. CONTRACT A 2006-09-01
5. CONTRACT B 2006-12-01
6. CONTRACT C 2007-03-01
7. CONTRACT A 2007-06-01
8. CONTRACT B 2007-09-01
9. CONTRACT C 2007-12-01
This time, I'm trying to select the first option after the current
date, but for each contract. So if I run the query with current date as
2006-10-09 the return would be
1. CONTRACT B 2006-12-01
2. CONTRACT C 2007-03-01
3. CONTRACT A 2007-06-01
Serge Rielau wrote:
th*******@gmail.com wrote:
I have a table with a number of fields, one being a date.
What I'm looking to do is take a date variable, and then select only
one row - that being the one that contains the next date value in
chronological order. So, as an example, if the table contains
1. 2005-12-01
2. 2006-03-01
3. 2006-06-01
4. 2006-09-01
5. 2006-12-01
6. 2007-03-01
7. 2007-06-01
8. 2007-09-01
I want to run the query and enter the date 2006-10-09, and have the
result returned
1. 2006-12-01
SELECT * FROM T
WHERE COLDATE ?
ORDER BY COLDATE
FETCH FIRST ROW ONLY
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
