• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Free DB2 forum - SQL error with rownumber() over

 
Ranch Hand
Posts: 281
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there any active and free database forum for DB2 question-answer postings?

I am getting error while running the following SQL in DB2 8.x in Z/OS

SELECT * FROM (SELECT MYCOL1, ROWNUMBER() OVER (ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLE ) AS PRODCT_TEMP WHERE ROW_NEXT BETWEEN 3 and 5

I am getting following error:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
your answer should be contained between the tokens in your sqlerrm.

SQL0104N An unexpected token "<token>" was found following "<text>". Expected tokens may include: "<token-list>".

Cause: A syntax error in the SQL statement was detected at the specified token following the text "<text>". The "<text>" field indicates the 20 characters of the SQL statement that preceded the token that is not valid.

As an aid to the programmer, a partial list of valid tokens is provided in the SQLERRM field of the SQLCA as "<token-list>". This list assumes the statement is correct to that point.

The statement cannot be processed.

Action: Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try it with ROW_NUMBER() instead of ROWNUMBER().
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sam,

Besides needing to post the sqlerrm text, I noticed some of your questions from what appears to be similar questions about the same problem.

i.e., do you need to use an order by clause? Yes, To skip records from a result set, you must impose order on the result set, otherwise there is no concept of first, second, next, or between.

When I try to resolve sql errors, i start from the inside and work my way out... in your case... does the inline view work correctly?

SELECT MYCOL1, ROW_NUMBER() OVER (ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLE

If you have no errors, then the problem is in your outer statement... but I would expect your errors are contained in your inner statement.
 
Sam Gehouse
Ranch Hand
Posts: 281
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, inner statement is showing error.
I get the following error :


SELECT ROW_NUMBER() OVER(ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLE

SQL0104N An unexpected token "(" was found following "". Expected tokens may
include: ", FROM INTO". SQLSTATE=42601

SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".
This list assumes the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601
 
Sam Gehouse
Ranch Hand
Posts: 281
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tried with:

SELECT MYCOL1, ROW_NUMBER() OVER(ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLE

Get the exact same sqlerrm as above

Any help will be appreciated.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have your answer... the row_number windowing function is not available in DB2 8.2 for z\OS (it is in the windows/'nix versions). You'll have to do it the old fashioned way.

From the DB2 Portal:


The next OLAP specification introduced by DB2 9 for z/OS is ROW_NUMBER. ROW_NUMBER specifies that a sequential row number is computed for the row that is defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in an arbitrary order, as the rows are returned. This satisfies an often-requested capability to simply assign a number to the result rows of a query. Row numbers also enable easy formulation of queries for computing histogram statistics and they enable formation of other OLAP specifications (for example, moving sums, moving averages, and so on).



select x.mycol1
from (
select a.mycol1,
(select count(*)
from mytable1 b
where b.mycol1 <= a.mycol1) as rn
from mytable1 a
) x
where x.rn between 3 and 5
[ January 08, 2008: Message edited by: Paul Campbell ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic