Db2 SQL: repeat row using CTE

Question

I use following (dummy) code to generate repeated row:

Results in error:

08:33:12 FAILED [WITH - 0 rows, 0.235 secs] 1) [Code: -343, SQL State: 42908] The column names are required for the recursive common table expression "myuser.CTE".. SQLCODE=-343, SQLSTATE=42908, DRIVER=4.28.11 2) [Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-343", SQLSTATE "42908" and message tokens "myuser.CTE".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.28.11

Any ideas please; Thanks

FYKI, the above code sample was created by ChatGPT. Insofar, it must be correct ;o)

Answer 1

Have you tried acting on what the message is saying?

"The column names are required for the recursive common table expression"

In other words, try "WITH CTE(row_number) ..."

However, your overall query makes little sense.

If you only want to see 5 table names, then your outer query can use fetch first 5 rows only and then you don't need the CTE at all, you only need the query on sysibm.systables with a fetch first clause.