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

Question

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)

Heinz Z. · Accepted Answer · 2012-11-16 10:38:34Z

4

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;

answered Nov 16, 2012 at 10:38

Heinz Z.

1,5943 gold badges15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clockwork-Muse Over a year ago

Personally, if I'd already declared a 'canonical' version (ie, the CHAR-accepting one), I'd just have the other versions to casts/conversions, and funnel into the original. However, good that you've found it - go ahead and accept your own answer when you're able.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related