Hi,

i have a problem with row_number(). The following query doesn't work:

SELECT USERS, ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM lib.tab FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS

And i get the following error-message(german-localisation):

SQL0104N Auf "" folgte das unerwartete Token "(". Zu den möglichen Token
gehören: ", FROM INTO". SQLSTATE=42601

It says: unexpected Token "(" - Possible Token: FROM INTO

Does anybody have a clue?

regards
.Ape

Hi,

Do you use Unix Command Windows? Please give more info. You can also read Must Read before posting for more tips.

Did you try to write the command with quotation marks e.g.:
db2 "SELECT USERS, ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM lib.tab FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS"

Hope this helps,
Grofaty

Hi,

sorry, i've read it now - shame on me!

We have as DB2 V8 (Release a.00 ) on AS/400 and i am connected from a Suse-Linux8.2 - box with installed DB2connect 8.1.

Yes I use Unix Command Windows.

Yes, I tried both directly from the command line (bash):

$ db2 'SELECT USERS, ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM lib.tab FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS'

and from inside of the client:

DB2 => SELECT USERS, ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM lib.tab FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS

but I always end up with the error-message.

regards
.Ape

Hi,

Is column USERS column in lib.tab table or you would like to get 'user' so the USER should be written?

Hope this helps,
Grofaty

Hi,

Sorry, I should have done copy and paste :-/

There is a column USER in lib.tab. Here again with copy and paste:

db2inst1@dev:~> db2 'SELECT USER,ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM lib.tab FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS'
SQL0104N Auf "" folgte das unerwartete Token "(". Zu den möglichen Token
gehören: ", FROM INTO". SQLSTATE=42601

I know that USER is a reserved word and that i should better use e. g.
SELECT lib.tab.USER FROM lib.tab ... but for my problem it doesnt matter, it appears when i add row_number() to the query.

Thank you very much for your help so far.

kind regards
.Ape

Hi,

I tested the following SQL (just replace your table with system table sysibm.sysdummy1):

"SELECT USER,ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM sysibm.sysdummy1 FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS"

Please try the above SQL. I tested it and it works fine for me.

Hope this helps,
Grofaty

Hi,

db2inst1@dev:~> db2 "SELECT USER,ROW_NUMBER() OVER(ORDER BY USER) AS RN FROM sysibm.sysdummy1 FETCH FIRST 5 ROWS ONLY OPTIMIZE FOR 5 ROWS"
SQL0104N Auf "" folgte das unerwartete Token "(". Zu den möglichen Token
gehören: ", FROM INTO". SQLSTATE=42601

the same result. Is maybe a libary missing in the server?

kind regards
.Ape