Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Unanswered: Creating User Defined function in DB2 v7.1.2 OS/390

    Hi All ,

    I am trying to create a simple function as below in DB2 v7.1.2 os/390 :

    CREATE FUNCTION hriscnv.kpTEST (IN_EMPLID VARCHAR(11) )
    RETURNS VARCHAR(1)
    LANGUAGE SQL
    SPECIFIC HRISCNV.kpTEST
    NOT DETERMINISTIC
    READS SQL DATA
    DECLARE NEW_EMPL_STATUS varCHAR(1) ;
    BEGIN
    SET NEW_EMPL_STATUS = ( select empl_status from HRISCNV.ps_job where emplid = in_emplid);
    END ;

    And I get the following error :

    Error: SQL0104N An unexpected token "NEW_EMPL_STATUS" was found following "". Expected tokens may include: "ON <INTEGER> ". SQLSTATE=42601
    (State:42601, Native Code: FFFFFF98)


    Any suggestions ?

    Thanks
    SK

    --------------------------------------------------------------------------------

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    AFAIK, you cannot do this on that version of DB2 on os/390 .. all you can do is

    create function ...
    ... blah .. blah ..
    returns
    <sql stmt>

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    other thing you could probably test is to move
    DECLARE NEW_EMPL_STATUS varCHAR(1) ;
    inside the compound BEGIN ... END block as the first stmt

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •