The sql you provided works. But when I put it into the following sql
as a subquery, i alwasy got error message:
SQL0104N An unexpected token "AS" was found
Is there limitation for DB2 WITH...AS... clause?
SELECT
T.a,
T.b,
(WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 = T.a
UNION ALL
SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 =
rec.c2)
SELECT c1, c2 FROM rec FETCH FIRST 1 ROWS ONLY
) as c
FROM T
thanks,
Michael
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...
Here is the SQL Standard version (also supported by SQL Server 2005)
which works in DB2 for LUW
WITH rec(c1, c2)
AS (SELECT c1, c2 FROM table WHERE c2 IN ('1','2')
UNION ALL
SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 = rec.c2)
SELECT c1, c2 FROM rec
Cheers
Serge