error creatinf function db2

please help - i get an error when creating a function:

CREATE FUNCTION GETCURRENTSYSDATE()
     RETURNS CHAR(8)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     RETURN CHAR(YEAR(current date))||char(month(current date))||char(day(current day))
   END

this gives the errors


DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END" was found following "r(day(current day))
".  Expected tokens may include:  "<delim_semicolon>".  LINE NUMBER=5.  
SQLSTATE=42601
LVL 1
hraja77Asked:
Who is Participating?
 
tliottaCommented:
CREATE FUNCTION GETCURRENTSYSDATE()
       RETURNS CHAR(8)
       LANGUAGE SQL
       DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
       BEGIN ATOMIC
        RETURN (select CHAR(YEAR(current date))||char(month(current date))||char(day(current date)) from SYSIBM.SYSDUMMY1);
     END

Open in new window

That works for me. I don't know what version of DB2 you're running nor what platform nor what client. But it ought to be very close.

Tom
0
 
Cenjoy100Commented:
Hi hraja77,
Try this,

CREATE FUNCTION GETCURRENTSYSDATE()
     RETURNS CHAR(800)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     RETURN select CHAR(YEAR(current date))||char(month(current date))||char(day(current day)) from SYSIBM.SYSDUMMY1;
   END
0
 
hraja77Author Commented:
db2 => CREATE FUNCTION GETCURRENTSYSDATE()
     RETURNS CHAR(800)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     RETURN select CHAR(YEAR(current date))||char(month(current date))||char(day(current day)) from SYSIBM.SYSDUMMY1;
   ENDdb2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "rom
SYSIBM.SYSDUMMY1".  Expected tokens may include:  "JOIN <joined_table>".  LINE
NUMBER=5.  SQLSTATE=42601
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Cenjoy100Commented:
CREATE FUNCTION GETCURRENTSYSDATE()
     RETURNS CHAR(800)
   
     BEGIN ATOMIC
     RETURN SELECT CHAR(YEAR(CURRENT_DATE))||char(month(CURRENT_DATE))||char(day(CURRENT_DATE)) from SYSIBM.SYSDUMMY1;
       END
   

Sorry for giving tries..As I dont have right now DB2 environment open so its giving you syntax errors.

Try this...else I will work on it by tomorrow.

Sorry for that.... Hope this will work :)
0
 
hraja77Author Commented:
db2 =>  CREATE FUNCTION GETCURRENTSYSDATE()
     RETURNS CHAR(800)
   
     BEGIN ATOMIC
     RETURN SELECT CHAR(YEAR(CURRENT_DATE))||char(month(CURRENT_DATE))||char(day(CURRENT_DATE)) from SYSIBM.SYSDUMMY1db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) =>
db2 (cont.) => ;
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END CREATE" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<create_or_replace>".  
SQLSTATE=42601
db2 =>
0
 
hraja77Author Commented:
no this should be deleted as the question is lightly diferent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.