RE: UDF for row_number() over()

I am not sure, but perhaps one of the QIBM_QZDA_SQLx exit points might allow you to intercept the offending statement and "revise" it at runtime.....

Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Pete Helgren
Sent: Monday, January 28, 2008 3:57 PM
To: Midrange Systems Technical Discussion
Subject: 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