Rank Function in Mainframe DB2

[pbibler]

I can't get the following query to work:

SELECT DIV_NO,ITM_NO, RANK() OVER (ORDER BY ITM_LAST_SH_DT) AS R1
FROM NOKDE.OKIITIO
WHERE ITM_NO= '15310'

It gives me a "Function Rank was not found" error. I thought Rank was an ANSI standard function. There's always the chance our system was modified somehow, because DB2 is the engine for our warehouse system, but any insights are welcome.

Thanks,

Paul

 

[blom0344]

Rank is available for DB2 UDB from version 7 onwards (AFAIK)
Other types - OS400 - and mainframe do not support OLAP type functions

Ties Blom

 

[pbibler]

I figured another way around it, but it's a nice function. Too bad. Thanks for your response, much appreciated.

Paul

 

[sathyarams]

Paul, Will it be possible to post your work-around please ??

It may help the rest of the community

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[blom0344]

Hi Sathyaram,

Are you back from a sabbatical?

Ties Blom

 

[sathyarams]

Ties, Thanks for remembering me ;-)

Lots been going on both professionally and personally ... And of course, I can't deny, my laziness ...

And in tek-tips, you rock ... Great ..

Have fun

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[pbibler]

Sorry, I'll post the work-around, I never got an email that you had posted this. Basically, what I wanted to do was select only the row that had the most recent date. So I created a subquery from the same table which had the max(date):

SELECT a.DIV_NO,a.ITM_NO
FROM NOKDE.OKIITIO a,
(select div_no,itm_no,max(last_ship_dt) as maxdate
FROM NOKDE.OKIITIO a group by 1,2) b

WHERE a.div_no=b.div_no
and a.itm_no = b.itm_no
and a.last_ship_dt = b.maxdate

Crude, but effective. There might be a more elegant way, but this seemed to work well enough.

 

[MarcLodge]

All,

For reference I've NEVER managed to get any of the OLAP functions RANK, ROW_NUMBER etc. to work on an OS390 type mainframe. The site I'm at at the moment is on DB2v8 and it doesn't work here either.

Marc