MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2010

AW: UDF Syntax



fixed

RESULT is a reserved word (SQL command SET RESULT SET!) and can't be used as
variable name.
The compiler expects SET RESULT SET instead of SET RESULT = !

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von ibm
Gesendet: Wednesday, 17. February 2010 20:13
An: Midrange Systems Technical Discussion
Betreff: UDF Syntax

What is wrong with the syntax of my UDF? The compiler throws on the SET
RESULT and SET RECCOUNT lines as well as on the IF statement. Do I need
to set up a cursor?

Thanks
chris


BEGIN
DECLARE RESULT DECIMAL ( 13 , 2 ) ;
DECLARE RECCOUNT INTEGER ;

SET RESULT = 0;
SET RECCOUNT = 0;

SELECT COUNT(*) INTO RECCOUNT FROM IWPREPORTS.BOOK07R_HELPER1 WHERE
ITCLS = PARAM_ITCLS;

IF RECCOUNT = 1 THEN
SELECT YTD INTO RESULT FROM IWPREPORTS.BOOK07R_HELPER1 WHERE
ITCLS = PARAM_ITCLS;
ELSEIF RECCOUNT = 0 THEN
SELECT
SUM(AMFLIB.MTHACTVY.INSAH) INTO RESULT
FROM
AMFLIB.MTHACTVY
WHERE
AMFLIB.MTHACTVY.ITCLS = PARAM_ITCLS AND
AMFLIB.MTHACTVY.TDATE BETWEEN PARAM_STARTDATE AND PARAM_ENDDATE
AND
( ( AMFLIB . MTHACTVY . SACUR >= 10 AND AMFLIB . MTHACTVY
. TDATE < PARAM_YEARSTART ) OR
( AMFLIB . MTHACTVY . SACUR < 10 AND AMFLIB . MTHACTVY .
TDATE >= PARAM_YEARSTART ) );
-- UPDATE TEMPORARY TABLE
INSERT INTO IWPREPORTS.BOOK07R_HELPER1 (ITCLS,YTD) VALUES
(PARAM_ITCLS,RESULT);
ENF IF;



RETURN RESULT ;
END





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact