Hi all,
I am getting this error when I tried to build a User defined Function. The code and error message is as follows:

CODE:
CREATE FUNCTION fnIsVowel
(
c CHAR(1)
)
RETURNS SMALLINT
SPECIFIC fnIsVowel
BEGIN ATOMIC
IF ( c = 'A' ) OR ( c = 'E' ) OR ( c = 'I' ) OR ( c = 'O' ) OR ( c = 'U' ) OR ( c = 'Y' ) THEN
RETURN 1;
END IF;
RETURN 0;
END

CREATE FUNCTION fnSlavoGermanic
(
Word CHAR(50)
)
RETURNS SMALLINT
SPECIFIC fnSlavoGermanic
BEGIN ATOMIC

--Catch NULL also...
IF ( LOCATE('W', Word) > 0 ) OR ( LOCATE('K', Word) > 0 ) OR ( LOCATE('CZ', Word) > 0 ) THEN
RETURN 1;
END IF;

RETURN 0;
END
ERROR MESSAGE
:
DB2ADMIN.fnIsVowel - Build started.
Create user-defined function returns -104.
DB2ADMIN.fnIsVowel: 20: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "CHAR" was found following "Word ". Expected tokens may include: ".". LINE NUMBER=20. SQLSTATE=42601

DB2ADMIN.fnIsVowel - Build failed.
DB2ADMIN.fnIsVowel - Roll back completed successfully.

Can someone point out what is wrong with this statements?

Thanks

not sure which db2 you are using.

I am pretty sure that for db2 z/OS, you cannot put logic in a scalar udf. That is, no support for such "IF --then " stmt yet.

There is a case-expression that can serve simple IF-THEN logic.

I would also use a CASE expression instead. The DB2 optimizer has a much better chance on optimizing code that does not contain procedural logic - as an SQL PL UDF would introduce.

Thank you guys. But this is being developed in a UDBv8.2 and v9.1environment.
So are does this mean that the error is misleading because it does not appear to like the first create function statement? That's where this problem is coming from...it like the first create function but not the second or does it? Why (see error message)? They are practically the same statement except for size of "c" and "word". This is the aggravating part of our job...something seemingly very clear is not!

CREATE FUNCTION fnIsVowel
(
c CHAR(1)
)
RETURNS SMALLINT
SPECIFIC fnIsVowel
BEGIN ATOMIC
IF ( c = 'A' ) OR ( c = 'E' ) OR ( c = 'I' ) OR ( c = 'O' ) OR ( c = 'U' ) OR ( c = 'Y' ) THEN
RETURN 1;
END IF;
RETURN 0;
END

CREATE FUNCTION fnSlavoGermanic
(
Word CHAR(50)
)
RETURNS SMALLINT
SPECIFIC fnSlavoGermanic
BEGIN ATOMIC

--Catch NULL also...
IF ( LOCATE('W', Word) > 0 ) OR ( LOCATE('K', Word) > 0 ) OR ( LOCATE('CZ', Word) > 0 ) THEN
RETURN 1;
END IF;

RETURN 0;
END

Now we're slowly getting to the point where you tell us what is happening. So we are on DB2 for Linux, and the error occurs both in V8 and V9.1? Do you get the error on the first CREATE FUNCTION statement or the second or both?

Hi Knut,
Good morning...I did find out that I needed to add a statement terminator after END in the first create function. That seem to have solved that problem...however I do have perhaps a more difficult error to resolve and I will be posting in another thread. Please visit the thread as I think you might be able to help...thanks