RE: UDF for row_number() over()

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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
>
>