By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,830 Members | 812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,830 IT Pros & Developers. It's quick & easy.

Row_number() over() not working in SPUFI

P: 14
Hello! I am using UDB 8 in Z/OS and I am trying to execute this statement in SPUFI
Expand|Select|Wrap|Line Numbers
  1. select dept, name, row_number() over()
  2. from staff
It just doesn't work. Here is the error message:
Expand|Select|Wrap|Line Numbers
  1. ---------+---------+---------+---------+---------+---------+---------+---------
  2.   SELECT DEPT, NAME, ROW_NUMBER() OVER() FROM DBPROD.STAFF;
  3. ---------+---------+---------+---------+---------+---------+---------+---------
  4. DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE
  5.          LEGAL ARE: , FROM INTO
  6. DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
  7. DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR
  8. DSNT416I SQLERRD    = 502  0  0  -1  39  0 SQL DIAGNOSTIC INFORMATION
  9. DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'  X'FFFFFFFF'
  10.          X'00000027'  X'00000000' SQL DIAGNOSTIC INFORMATION
Do I need to set something on? Or the command can't be executed in SPUFI?
Is there other command I can execute which will give me the same results?

thanks!
Karen
Oct 2 '08 #1
Share this Question
Share on Google+
1 Reply


docdiesel
Expert 100+
P: 297
Hi Karen,

I'm sorry, but ROW_NUMBER() OVER() isn't supported in DB2 v8 for z/OS. While it's included in v8 for Linux, Unix and Windows, for z/OS it's been introduced with v9. See also the IBM Redbook "DB2 9 for z/OS Technical Overview", p. 19:

Data warehousing and reporting
* SQL enhancements
- EXCEPT and INTERSECT,
- OLAP specifications: RANK, DENSE_RANK, ROW_NUMBER
- Cultural sort
- Caseless comparisons
- FETCH FIRST in full select

Regards

Doc Diesel
Oct 4 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.