Re: UDF for row_number() over()

"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