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



Hi List,

I have a function that will always return the same result if the input
criteria is the same. After running it and noticing the IO for that file was
too high, I changed the function to deterministic, hoping it would cache the
result after the first call. That doesn't seem to be the case...

Here is the function:

CREATE FUNCTION DataWH/CatCodes
(x_SY varCHAR(4),
x_RT varCHAR(2),
x_KY varCHAR(10))
RETURNS varCHAR(40)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
Begin

DECLARE x_Des varCHAR(50);
DECLARE at_end INT DEFAULT 0;
DECLARE not_found
CONDITION FOR '02000';

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

SELECT DRDL01
INTO x_Des
FROM F0005
WHERE DRSY = x_SY
and DRRT= x_RT
and LTRIM(DRKY) = ltrim(rtrim(x_KY));


IF at_end <> 0 THEN
return 'N/A';
else
return ltrim(rtrim(x_KY))||' - '||ltrim(rtrim(x_Des));
END IF;
END

Is there a reason why the table IO still too high?

Thanks, Wes




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.