4

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?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Boris
  • 76
  • 1
  • 1
  • 5
  • Thanks for the answers. I just forgot to copy the `ONLY` keyword. If I add it the query still does not work! Same error message. – Boris Sep 28 '11 at 13:07

3 Answers3

7

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;
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 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. – Boris Sep 28 '11 at 13:09
0

Missing the Only Keyword at the end. Example here.

Clarkey
  • 1,553
  • 5
  • 22
  • 34
0

While the example you give is likely simplified, how about putting the fetch first clause in the first select portion?

I can never read the documentation clearly, but as the with-statement creates a common-table-expression, you might not be able to use the fetch-first-clause on selecting from it. According to this documentation, having the fetch-first-clause in the select of the with-statement is valid syntax.

i.e.

WITH a AS (
SELECT * FROM sysibm.systables
FETCH FIRST 10 ROWS ONLY
)
SELECT a.* FROM a;
Clarkey
  • 1,553
  • 5
  • 22
  • 34