Re: UDF for row_number() over()

OK! I created a row_number() function that will work (thanks to the archives and Alan Campin). The wrinkle here is that the SQL client application is passing "row_number() over()" to the System i (at V5R3M0). I don't think that it can accept 2 functions in a row without a comma separating them. That is, row_number() over() won't work on V5R3M0 even if I build UDF's to accommodate them [The error is: Token ( was not valid. Valid tokens: , FROM INTO.] BUT row_number(), over() would work.

IOW, I am trying to allow the V5R3M0 box accept the row_number() over() syntax using UDF's so that I can run the same statement on both a V5R4M0 box that supports row_number() over() natively and V5R3M0 which doesn't.

Am I trying to do the impossible?

Pete Helgren


Elvis Budimlic wrote:

Using SCRATCHPAD you can approximate ROW_NUMBER functionality. Perhaps
that'll suffice for your requirement. Here is a link to an IBM example how
to create a Counter UDF (Counts rows in a result set):

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyexamud
fcounter.htm

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: UDF for row_number() over()

Another compelling reason to move to V5R4M0 ! Looks like DB2 for i5/OS in V5R4M0 has some great enhancements. However, I need to support some V5R3M0 customers until they move and I have an SQL statement that uses row_number() over() functions that works slick. Has anyone been able to build a UDF in V5R3M0 that approximates this functionality? RRN is probably as close as I'll get but I wonder if someone has a solution that is a bit more complete.

Thanks,

Pete Helgren