Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Baden-Baden, Germany
    Posts
    4

    Unanswered: Problem with row_number()

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Oct 2003
    Location
    Baden-Baden, Germany
    Posts
    4
    Hi,

    Originally posted by grofaty
    Do you use Unix Command Windows? Please give more info. You can also read Must Read before posting for more tips.
    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.

    Originally posted by grofaty
    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
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    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

  5. #5
    Join Date
    Oct 2003
    Location
    Baden-Baden, Germany
    Posts
    4
    Hi,

    Originally posted by grofaty
    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
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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

  7. #7
    Join Date
    Oct 2003
    Location
    Baden-Baden, Germany
    Posts
    4
    Hi,

    Originally posted by grofaty
    ...

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

    Grofaty
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •