Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

JDBC

Database access, JDBC/ODBC, could even brush on some SQL

Free DB2 forum - SQL error with rownumber() over


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/OSSELECT * FROM (SELECT MYCOL1, ROWNUMBER() OVER (ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLE ) AS PRODCT_TEMP WHERE ROW_NEXT BETWEEN 3 and 5I am getting following error:Examine and correct the statement in the area of the specifiedtoken.sqlcode :  -104sqlstate :  42601

   
   

   
   
      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: -104sqlstate: 42601

   
   

   
   
      Try it with ROW_NUMBER() instead of ROWNUMBER().

   
   

   
   
      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.MYTABLEIf you have no errors, then the problem is in your outer statement... but I would expect your errors are contained in your inner statement.

   
   

   
   
      Yes, inner statement is showing error.I get the following error :SELECT ROW_NUMBER() OVER(ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLESQL0104N  An unexpected token "(" was found following "".  Expected tokens mayinclude:  ", FROM INTO".  SQLSTATE=42601SQL0104N  An unexpected token "(" was found following "".  Expected tokens may include:  ", FROM INTO".Explanation:A syntax error in the SQL statement was detected at the specifiedtoken following the text "<text>".  The "<text>" field indicatesthe 20 characters of the SQL statement that preceded the tokenthat is not valid.As an aid to the programmer, a partial list of valid tokens isprovided 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 specifiedtoken.sqlcode :  -104sqlstate :  42601

   
   

   
   
      Tried with:SELECT MYCOL1, ROW_NUMBER() OVER(ORDER BY MYCOL1) AS ROW_NEXT FROM MYSCHEMA.MYTABLEGet the exact same sqlerrm as aboveAny help will be appreciated.

   
   

   
   
      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:quote: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.mycol1from (select a.mycol1,(select count(*)from mytable1 bwhere b.mycol1 <= a.mycol1) as rnfrom mytable1 a) xwhere x.rn between 3 and 5[ January 08, 2008: Message edited by: Paul Campbell ]


Related Links

need to create object of table
using field value
Pl. suggest a logic
Only for JDBC tallents
Scrollable ResultSet
Sample code for using JMS with JBoss
combining LEFT OUTER JOIN and WHERE
Transaction Issues with session EJB.
Stored procedres
stange problem with MySql driver
Suitable Database
ResultSet --> ArrayList or Vector
Read SQL File
Prepared statement
SQL Exception
is bridge driver a bridge???