1

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)

WarrenT
  • 4,502
  • 19
  • 27
Heinz Z.
  • 1,537
  • 3
  • 14
  • 29

1 Answers1

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;
Heinz Z.
  • 1,537
  • 3
  • 14
  • 29
  • 2
    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. – Clockwork-Muse Nov 16 '12 at 17:36