Query from ORACLE board is not working in DB2

Hi everybody!
This query is supposed to count consecutive years from the current year
without OLAP.

Input Table:

ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06/30/2000
4 08/01/1999

Requested output:

ID ConYears
1 3
2 2
3 1

The solution uses a CROSS JOIN and a LEFT OUTER JOIN. The CROSS JOIN creates
all possible combinations of ID and year. Then the LEFT OUTER JOIN attempts
to match each such ID and year combination to a row in the data table.

WITH DATATABLE(ID, DateCol) AS
(VALUES(1, '2006-02-01'),
(1, '2006-01-01'),
(1, '2005-01-01'),
(1, '2004-01-01'),
(1, '1999-01-01'),
(2, '2006-02-01'),
(2, '2005-01-01'),
(3, '2006-04-01'),
(3, '1999-04-01'),
(4, '2000-06-30'),
(4, '1999-08-01')),
INTEGERS(I) AS
(VALUES(0),(1), (2),(3),(4),(5) ,(6),(7),(8),(9 ))
select X.ID , max(X.yr) as FirstMissing, year(current_da te) - max(X.yr) as
ConYears
from (select ID, year(current_da te) - i as yr
from integers
cross join
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current_da te) - max(X.yr) 0;

The derived table called X contains each combination of ID and year. This is
the left table in the outer join, and it is joined to the data table, such
that it matches the ID and year of the data. Note that it doesn't matter if
more than one row of the data table matches, as is the case in your original
data for ID 1 and year 2006.
Where a matching row is not found, using the IS NULL condition in the WHERE
clause, that combination of ID and year is retained (matching rows are
filtered out), and then, using a GROUP BY, only the maximum year which was
not found for each ID is chosen, and the number of consecutive years
calculated for each ID. Finally, the HAVING clause rejects any IDs like 4
which had 0 consecutive years from the current year.

ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1
When i tested this query: i got an empty output:

ID FIRSTMISSING CONYEARS
----------- ------------ -----------

0 record(s) selected.

Any idea why it is not working?
Thank's in advance. Leny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1