Row_number() over() not working in SPUFI

[karpalmera]

Hello! I am using UDB 8 in Z/OS and I am trying to execute this statement in SPUFI

select dept, name, row_number() over()
from staff

It just doesn't work. Here is the error message:

---------+---------+---------+---------+---------+---------+---------+---------
  SELECT DEPT, NAME, ROW_NUMBER() OVER() FROM DBPROD.STAFF;
---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE
         LEGAL ARE: , FROM INTO
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = 502  0  0  -1  39  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'  X'FFFFFFFF'
         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

[docdiesel]

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