Re: UDF for row_number() over()

[Pete Helgren <Pete@xxxxxxxxxx>]

"Yes, you're trying the impossible :)" 

My usual approach....

"Can you modify the client so it calls your new UDF instead?"

Yep.  That is the next step.  I can intercept the client request before 
it even gets to the System i but the challenge is to interrogate the 
database or set a property on the client that says "Yeah, I am a V5R3M0 
DB2 database so don't even try a row_number() over() call.  I should be 
able to use RegEx to find the statement and add a "," between the two 
start with.  What I need to do is also create a UDF that will take a 
string passed by over() and return nothing since it looks like the 
row_number() works in conjunction with over() and uses whatever is 
passed in over() to partition or order the rows.

More work to do. 

Thanks,

Pete


Elvis Budimlic wrote:
> Yes, you're trying the impossible :)
>
> Can you modify the client so it calls your new UDF instead?
>
> Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
> www.centerfieldtechnology.com
>  
>
> -----Original Message-----
> 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
>
>