Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    12

    Question Unanswered: Can't call the user defined function

    Dear Sir,

    I can create a user-defined function and deployed successsfully, but error message "The function "SF_MASKID" resolved to specific function "SQL111026095035700" that is not valid in the context where it is used .. SQLCODE=390, SQLSTATE=42887,DRIVER=4.12.55. while I call it.

    The function as below.

    CREATE OR REPLACE FUNCTION SF_MASKID (INOUT P_ID VARCHAR(17))
    RETURNS VARCHAR(17)
    NO EXTERNAL ACTION
    LANGUAGE SQL
    BEGIN
    SET P_ID = SUBSTR(P_ID,1,4) CONCAT TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****');
    RETURN P_ID;
    END

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    that is not valid in the context where it is used ..
    So, please show in what context did you used the function.
    In other words, show the SQL statement in which you used the function.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Apart from your issue, you might want to use
    TRANSLATE( SUBSTR(P_ID,5) , '**********' , '1234567890' )
    or
    TRANSLATE( SUBSTR(P_ID,5) , '' , '1234567890' , '*' )
    than
    TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****')

    Because, your original use of TRANSLATE function translates '*' into '1'.
    (Second parameter and third parameter of TRANSLATE function of DB2 are reversed to Oracle.)

  4. #4
    Join Date
    Oct 2011
    Posts
    12
    Quote Originally Posted by tonkuma View Post
    Apart from your issue, you might want to use
    TRANSLATE( SUBSTR(P_ID,5) , '**********' , '1234567890' )
    or
    TRANSLATE( SUBSTR(P_ID,5) , '' , '1234567890' , '*' )
    than
    TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****')

    Because, your original use of TRANSLATE function translates '*' into '1'.
    (Second parameter and third parameter of TRANSLATE function of DB2 are reversed to Oracle.)
    I test it in DB2 editor. By the way, I set the P_ID is 'G12345678' and run below for testing, the return result is G123***** that is my expected result.

    select SUBSTR(P_ID,1,4) concat TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****') from sysibm.dual;

    Is it wrong ?

    Thanks for your help.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that you must be setting compatibility vector to Oracle, like I wrote in another thread.
    This syntax is valid only in Oracle or (may be valid) in DB2 setting compatibility vector to Oracle.
    I don't know much about bahavior of DB2 setting compatibility vector.
    So, I would not comment any more.

    Sorry, I might confused you.

  6. #6
    Join Date
    Oct 2011
    Posts
    12
    Quote Originally Posted by amcc38 View Post
    I test it in DB2 editor. By the way, I set the P_ID is 'G12345678' and run below for testing, the return result is G123***** that is my expected result.

    select SUBSTR(P_ID,1,4) concat TRANSLATE(SUBSTR(P_ID,5),'1234567890','*****') from sysibm.dual;

    Is it wrong ?

    Thanks for your help.
    Assume the SQL is valid, is it something I still missing when create the common function ? I tried to add the schemas name together with the function name like "DEVDB.SF_MASKID", but I still get the same error message. Anyone can help ?

    Thanks !

Posting Permissions

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