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



Birgitta,

Here's the statement: http://code.midrange.com/index.php?id=2b3d23e160  

The statement is indeed in a SQLRPGLE and the programmer did end up
using numeric host variables to compare to (like I suggested to the
programmer who wrote this statement, eventually) but there is a join to
a table that contains real date fields to match to numeric date fields.
Nothing can be done about the different data types for the date fields,
yet.  By the way, the statement results are inserted into a work file to
be used by other programs.    

SQL statement back ground. We needed to extract vendor rebate totals and
item sales totals information summed for a rebate period.   The rebate
or item sales totals can be null to indicate none for that period.  

I in no way am condemning your posted UDFs or the use of them; I was
just sharing my first real time experience of UDFs. I have often seen
UDFs in articles and discussions and thought that they would have
potential for our shop.  You post for the date SQL UDF meet the
requirement that lit my fuse and I pushed to have it accepted in a
statement I thought it would fit.  I was completely disappointed by the
performance.   

The performance using the SQL UDF was, to put it mildly, appalling.  So
bad in fact the first test using the SQL UDF we let run for over an hour
before killing it.  Before and after that, not using the SQL UDF it
would run in about 1 minute.

I created some tests using SQL and RPGLE UDFs and some more using
straight data conversion syntax.  Overwhelmingly, the tests using the
data conversions within the SQL statement performed better.   These
statements are very simple test.  The UDFs were using only in the
results set like,  SELECT Fld1, YYMDToDate(NumDate) FROM File.  Running
it over 1,000,000+ records I never let the SQL UDFs finish (after
waiting for 10 minutes verse 1 or 2 I did not see the point in letting
it finish). 

Thanks, Matt



As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.