CODEWITHSUNDEEP
db2ibm-midrangeparameterscreate-function
Heinz Z.
Heinz Z.

Reputation: 1574

Can not call a user defined function with CHAR parameter on DB2 for i

I create a function in DB2 (Version 6.01) this way:

CREATE FUNCTION myschema.test_c_c(param CHAR(1)) RETURNS CHAR(1) RETURN param;

and get a succses message and "System i Navigator" show me that function under myschema. But when I try to call this function this way:

SELECT myschema.test_c_c('X') FROM SYSIBM.SYSDUMMY1;

I get following message:

[SQL0204] TEST_C_C der Art *N in myschema nicht gefunden. 
[SQL State=42704, DB Errorcode=-204]

"nicht gefunden" equals to "not found".

Explanation of error code -204 (part of it, full error code Description here)

"The object identified by name is not defined in the DB2® subsystem."

And at last, with INT parameter all works fine:

CREATE FUNCTION myschema.test_i_ri(param INT) RETURNS INT RETURN param;
SELECT myschema.test_i_ri(4711) FROM SYSIBM.SYSDUMMY1;
CREATE FUNCTION myschema.test_ii_ri(param1 INT, param2 INT) RETURNS INT RETURN param1 +param2;
SELECT myschema.test_ii_ri(800, 15) FROM SYSIBM.SYSDUMMY1;

Where I miss the point?

I execute all statmentens with SQL Workbench/J (which I find very usefull)

Upvotes: 1

Views: 7256

Answers (1)

Heinz Z.
Heinz Z.

Reputation: 1574

After hours of try error and research I ask the question and then 30 minutes later I found the answere by myself.

The problem is the resolution of overloaded functions in DB2. When calling

SELECT myschema.test_c_c('X') FROM SYSIBM.SYSDUMMY1;

this way, DB2 interpret 'X' as VARCHAR but I have no version of test_c_c with VARCHAR params. What work is 

SELECT CESDTA.TEST_C_C(CAST('X' AS CHAR(1))) FROM SYSIBM.SYSDUMMY1;

Or declare a VARCHAR function:

CREATE FUNCTION cesdta.test_v_v(param VARCHAR(1)) RETURNS VARCHAR(1000) RETURN param;
SELECT cesdta.test_v_v('X') FROM SYSIBM.SYSDUMMY1;

Upvotes: 4

Related Questions