Hi all

Im new to db2 functions...
i want to create a function to input a date and check the input with database and if the value is null then return the input with con-cat of chars else db value + 1

here is what i wrote

CREATE FUNCTION "DB2ADMIN"."GET_NEXT_SCH_ID" ( "PDATE" INTEGER )
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL

BEGIN ATOMIC

DECLARE X INTEGER;

SET X = (SELECT MAX(SCHEDULE_ID)FROM PROGRAMME_SCHEDULE_HDR WHERE SCHEDULE_DATE = PDATE);

IF (X = NULL) THEN
RETURN SELECT RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001'FROM SYSIBM.SYSDUMMY1;

ELSE

RETURN (X + 1);

END IF;

END;

im getting a error of
DB2 Database Error: ERROR [42818] [IBM][DB2/NT] SQL0401N The data types of the operands for the operation "=" are not compatible. LINE NUMBER=10. SQLSTATE=42818

Plz provide your expert reviews

Use the IS operator to compare with a NULL value.

Thanx for the quick reply

i change the code as follows


CREATE FUNCTION "DB2ADMIN"."GET_NEXT_SCH_ID" ( "PDATE" INTEGER )
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL

BEGIN ATOMIC

DECLARE X INTEGER;

SET X = (SELECT MAX(SCHEDULE_ID)FROM PROGRAMME_SCHEDULE_HDR WHERE SCHEDULE_DATE = PDATE);

IF (X IS NULL) THEN
RETURN SELECT RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001'FROM SYSIBM.SYSDUMMY1;

ELSE

RETURN (X + 1);

END IF;

END;

but still same result

What is the schedule_date datatype in the database? Is it integer?

Another point, you define 'return int' but returning a character.

____

I try to cast it to INTEGER using follows

SELECT CAST((RTRIM(REPLACE(CHAR(PDATE),'-',''))||'0001')AS INTEGER)FROM SYSIBM.SYSDUMMY1

but this will return

DB2 Database Error: ERROR [22003] [IBM][DB2/NT] SQL0413N Overflow occurred during numeric data type conversion. SQLSTATE=22003

Other question was about SCHEDULE_DATE. Its TIMESTAMP

If you asking the SCHEDULE_ID its a integer column

any solutions...???

Schedule_date is compared against int. Thats what your original prob was about. I didnt mean to ask a question, put tried to prompt you to look for soln :-)

THANK FOR THE HELP sathyaram_s

So iz there is a solution for this one..
I mean if you can give a solution through a example that would be easy..

Thanx in advance

There are some problems in your original function definition.

1) Do not use double quotations.
Not all features that are legal are encouraged....
comp.databases.ibm-db2 | Google Groups
Max and group by gives error

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

Thanx for the wonderful advice and example

It's now working and i got a good chance to make my mistakes correct..

Thank you for all who helped me...

Alternate expressions for
may be
Another alternative way may be replacing whole return statement by