Why does FETCH FIRST N ROWS not work in combination with WITH statement?

Question

I have the following SQL statement which does not run on my DB2 database:

WITH a AS (
    SELECT * FROM sysibm.systables
)
SELECT a.* FROM a
FETCH FIRST 10 ROWS

Without the FETCH statement it works. The error message I get is:

Illegal use of keyword OPTIMIZE, token ERR_STMT WNG_STMT GET SQL SAVEPOINT HOLD FREE ASSOCIATE was expected.

Any suggestions?

Solution

You're missing the ONLY keyword at the end of the FETCH clause.

WITH a AS (
    SELECT * FROM sysibm.systables
)
SELECT a.* FROM a
FETCH FIRST 10 ROWS ONLY;

Comments

[Boris]

I only forgot to copy the ONLY here. I used it in the original statement and the query did not work. I have the feeling it is connected to the WITH clause.