File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Free DB2 forum - SQL error with rownumber() over Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Free DB2 forum - SQL error with rownumber() over" Watch "Free DB2 forum - SQL error with rownumber() over" New topic
Author

Free DB2 forum - SQL error with rownumber() over

Sam Gehouse
Ranch Hand

Joined: Jul 21, 2003
Posts: 281
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
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Oct 06, 2007
Posts: 338
Try it with ROW_NUMBER() instead of ROWNUMBER().
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Jul 21, 2003
Posts: 281
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

Joined: Jul 21, 2003
Posts: 281
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

Joined: Oct 06, 2007
Posts: 338
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Free DB2 forum - SQL error with rownumber() over