[karpalmera]

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

[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