2) Although PDATE was decared INTEGER,
you used expression REPLACE(CHAR(PDATE),'-','').
So, I guessed that you want to declare PDATE as DATE datatype.
2-1) It is understandable to get overflow for CAST((RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001')AS INTEGER).
Because, if PDATE was INTEGER it may return 14(= 10 + 4) characters,
if PDATE was DATE it may return 12(= 8 + 4) characters.
But, max number of digits in INTEGER is 10.
3) You accessed a table.
So you should specify READS SQL DATA instead of CONTAINS SQL.
4) Please don not declare and use unnecessary variables.
This is a good programming practice in almost every languages, not specific to SQL.
Note: If an expression was used twice or more(and the expression was not too simple), declare a variable for the expression.
Example of revised function:
(Not tested. Datatypes were guessed.)
CREATE FUNCTION DB2ADMIN.get_next_sch_id( pdate DATE )
DETERMINISTIC NO EXTERNAL ACTION
READS SQL DATA
1 + (SELECT MAX(schedule_id)
WHERE schedule_date = pdate )
, BIGINT( RTRIM( REPLACE( CHAR(pdate) , '-' , '' ) ) || '0001' )
Last edited by tonkuma; 09-02-11 at 18:05.
Reason: Add Note for 4).