Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Error while numbering returned rows sequentially

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Thu Feb 28, 2008 6:10 pm    Post subject: Error while numbering returned rows sequentially
Reply with quote

I want to number the returned rows from a select query sequentially.

SELECT C_ID,S_SEQ,
ROWNUMBER() OVER (ORDER BY S_SEQ DESC) AS NUM
FROM VNT;

I am getting the following error:

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO


Can any one please correct my query.
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Feb 28, 2008 6:19 pm    Post subject:
Reply with quote

Code:
ROWNUMBER()

OVER (...)

Are these two user defined functions?
Back to top
View user's profile Send private message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Thu Feb 28, 2008 6:24 pm    Post subject: Reply to: Error while numbering returned rows sequentially
Reply with quote

No they are not user defined. I searched in net and got his syntax.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Feb 28, 2008 6:57 pm    Post subject:
Reply with quote

For which vendors version of SQL?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Feb 28, 2008 7:05 pm    Post subject:
Reply with quote

Is this okay?
Code:
SELECT C_ID,
S_SEQ,
ROW_NUMBER() OVER (ORDER BY S_SEQ DESC) AS NUM
FROM VNT;
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Feb 28, 2008 7:31 pm    Post subject:
Reply with quote

I think your net search was wrong. That seems more like Oracle syntax to me.

Have you looked in the DB2 SQL Reference Manual? I did a search of it for Row_Number and it returned 0 results for it as a function to number rows. I also did OVER( and got no results.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Feb 28, 2008 7:40 pm    Post subject:
Reply with quote

I got these info by google-ing through.

ROW_NUMBER() OVER (...) is a OLAP function available with DB2 for UNIX, Windows, and OS/2 as I guess.

And

ROW_NUMBER() OVER tells DB2 how you want the rows numbered. If you are selecting Employee data, telling it to ROW_NUMBER() OVER (ORDER BY SALARY DESC) would have the row with the highest SALARY as #1, the second highest as #2, etc.

Select the exact same data with ROW_NUMBER() OVER (ORDER BY LASTNAME) would have a person with a LASTNAME starting with 'A' as #1 and someone with 'Z' as the very last.

Please try these links.

http://www.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0401kuznetsov/index.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts OO COBOL compile error Virendra Shambharkar COBOL Programming 3 Tue Jan 10, 2017 6:05 pm
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us