× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.