rank(), denseRank() etc...

[tpass001]

Hi all,

I am trying to learn DB2 on os390 and this is my version is :
Database server = DB2 OS/390 7.1.1

I was trying to run this query
=====================
SELECT EMPNO, RANK() OVER(ORDER BY EMPNO) AS RANK# ,DENSE_RANK() OVER(ORDER BY EMPNO) AS DENSE# FROM DSN8710.EMP ORDER BY EMPNO
=====================
and i get this error
====================
DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO ". SQLSTATE=42601
==================

Any ideas why I am getting this error? I think 7.1.1 should support these functions? I tried these on 3270 but I got the same errors.

Thanks.

 

[blom0344]

Looks to me that your syntax is correct, at least it is for DB2 UDB.

Are you sure that DB2 for OS/390 does support OLAP functions?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

It looks correct to me also, from what I have read. The OS390 site that I am at does not seem to allow this statement either, but I can find no information to say that this statement is banned on OS390's. Curious.

 

[tpass001]

I am not sure if it does. I am new to mainframe development. Does anyone know if it does?

 

[sathyarams]

SQL Cross Platform Reference does not list this RANK function ... So I assume it is not suppported for OS/390

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8