Re: UDF for row_number() over()

[Pete Helgren <Pete@xxxxxxxxxx>]

Eric,

I can try to modify it at the client but your approach is my backup plan. 

Pete


DeLong, Eric wrote:
> 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
> >
> >   
> >