Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2009
    Posts
    33

    Unanswered: need help with functions

    i am getting an error while calling a function inside a function and assigning the value to a variable. the parameters passed are varchar and returns integer.
    SQL0440N No authorized routine named "func name" of type "FUNCTION" having compatible arguments was found. Here the input parameters of both the functions are varchar and output of both the functions are INTEGER. pls find the func defn and the commented lines are due to conversion of functions from oracle to db2 using migration tool kit. Pls assist in resolving the issue. thanks in advance sharath
    ------------------------------------------------
    the error is during execution of this function indacc and the other function name SYNC_INTCAL_TBL . similarly we have other functions which will be called later i will face same issues there too.if you can help me resolve this those things also will be resolved I hope.

    CREATE or REPLACE function SYNC_INTCAL_TBL ( INOUT IN_CB_FIN_ACCTNO CHAR(20),
    OUT RETURN_VAL DECIMAL(31,0))
    RETURNS INTEGER

    -------

    CREATE OR REPLACE FUNCTION SYNC_INDACC_TBL ( INOUT IN_CB_INDIVIDUAL_ACCTNO CHAR(20),
    OUT RETURN_VAL DECIMAL(31,0))
    RETURNS INTEGER
    MODIFIES SQL DATA
    LANGUAGE SQL

    BEGIN



    DECLARE NUMVAR DECIMAL(31,0) DEFAULT 0.0;

    DECLARE SQLCODE INTEGER DEFAULT 0;

    DECLARE SQL_ROWCOUNT INTEGER;

    BEGIN


    DECLARE temp_CB_INDIVIDUAL_ACCTNO CHAR(20);

    NUMBER(1,0);

    DECLARE temp_CB_INDIVIDUAL_ACCT_TYPE SMALLINT;


    DECLARE temp_CB_FIN_ACCTNO CHAR(20);


    DECLARE temp_CB_LINE_LIMIT BIGINT;


    DECLARE temp_CB_TEMP_LINE_LIMIT BIGINT;


    DECLARE temp_CB_TEMP_START_DATE CHAR(8);



    DECLARE temp_CB_TEMP_END_DATE CHAR(8);


    DECLARE temp_CB_OUTSTD_BAL DECIMAL(14,3);



    DECLARE temp_CB_FEE_WAIVER_SPEND DECIMAL(14,3);


    DECLARE temp_CB_IND_CARDHOLDER_NO CHAR(19);


    DECLARE temp_CB_RSKPRF_ID CHAR(10);


    DECLARE temp_CHECKSUM VARCHAR(20);


    DECLARE temp_CB_SYNCDATE CHAR(8);

    DECLARE var_count INTEGER;


    DECLARE CINDACC_FOUND INTEGER DEFAULT NULL;

    DECLARE SQLERRM VARCHAR(255);



    DECLARE cindacc CURSOR FOR SELECT CB_INDIVIDUAL_ACCTNO,
    CB_INDIVIDUAL_ACCT_TYPE,
    CB_FIN_ACCTNO,
    CB_LINE_LIMIT,
    CB_TEMP_LINE_LIMIT,
    CB_TEMP_START_DATE,
    CB_TEMP_END_DATE,
    CB_OUTSTD_BAL,
    CB_FEE_WAIVER_SPEND,
    CB_IND_CARDHOLDER_NO,
    CB_RSKPRF_ID,
    CB_SYNCDATE,
    CHECKSUM
    FROM CP_INDACC_TEMP
    WHERE CB_INDIVIDUAL_ACCTNO = IN_CB_INDIVIDUAL_ACCTNO;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

    BEGIN

    GET DIAGNOSTICS EXCEPTION 1 SQLERRM = MESSAGE_TEXT;


    SET NUMVAR = 0;


    --| CALL ORA.PUT_LINE(SQLERRM);


    CLOSE cindacc;

    END;


    OPEN cindacc;

    LOOP_LABEL:
    LOOP


    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET CINDACC_FOUND = SQLCODE;
    SET CINDACC_FOUND = 0;
    FETCH FROM cindacc INTO temp_CB_INDIVIDUAL_ACCTNO,
    temp_CB_INDIVIDUAL_ACCT_TYPE,
    temp_CB_FIN_ACCTNO,
    temp_CB_LINE_LIMIT,
    temp_CB_TEMP_LINE_LIMIT,
    temp_CB_TEMP_START_DATE,
    temp_CB_TEMP_END_DATE,
    temp_CB_OUTSTD_BAL,
    temp_CB_FEE_WAIVER_SPEND,
    temp_CB_IND_CARDHOLDER_NO,
    temp_CB_RSKPRF_ID,
    temp_CB_SYNCDATE,
    temp_CHECKSUM;
    END;


    IF CINDACC_FOUND = 100 THEN

    LEAVE LOOP_LABEL;

    END IF;

    --| UPDATE CP_INDACC SET
    --| CB_FIN_ACCTNO = temp_CB_FIN_ACCTNO,
    --| CB_LINE_LIMIT = temp_CB_LINE_LIMIT,
    --| CB_TEMP_LINE_LIMIT = temp_CB_TEMP_LINE_LIMIT,
    --| CB_TEMP_START_DATE = temp_CB_TEMP_START_DATE,
    --| CB_TEMP_END_DATE = temp_CB_TEMP_END_DATE,
    --| CB_OUTSTD_BAL = temp_CB_OUTSTD_BAL,
    --| CB_FEE_WAIVER_SPEND = temp_CB_FEE_WAIVER_SPEND,
    --| CB_IND_CARDHOLDER_NO = temp_CB_IND_CARDHOLDER_NO,
    --| CB_RSKPRF_ID = temp_CB_RSKPRF_ID,
    --| CB_SYNCDATE = temp_CB_SYNCDATE,
    --| CHECKSUM = temp_CHECKSUM
    --| where CB_INDIVIDUAL_ACCTNO = temp_CB_INDIVIDUAL_ACCTNO AND CB_INDIVIDUAL_ACCT_TYPE=temp_CB_INDIVIDUAL_ACCT_TY PE;


    UPDATE CP_INDACC
    SET CB_FIN_ACCTNO = temp_CB_FIN_ACCTNO, CB_LINE_LIMIT = temp_CB_LINE_LIMIT, CB_TEMP_LINE_LIMIT = temp_CB_TEMP_LINE_LIMIT, CB_TEMP_START_DATE = temp_CB_TEMP_START_DATE, CB_TEMP_END_DATE = temp_CB_TEMP_END_DATE, CB_OUTSTD_BAL = temp_CB_OUTSTD_BAL, CB_FEE_WAIVER_SPEND = temp_CB_FEE_WAIVER_SPEND, CB_IND_CARDHOLDER_NO = temp_CB_IND_CARDHOLDER_NO, CB_RSKPRF_ID = temp_CB_RSKPRF_ID, CB_SYNCDATE = temp_CB_SYNCDATE, CHECKSUM = temp_CHECKSUM
    WHERE CB_INDIVIDUAL_ACCTNO = temp_CB_INDIVIDUAL_ACCTNO
    AND CB_INDIVIDUAL_ACCT_TYPE = temp_CB_INDIVIDUAL_ACCT_TYPE;

    GET DIAGNOSTICS SQL_ROWCOUNT = ROW_COUNT;

    --| IF SQL%rowcount = 0 then

    IF SQL_ROWCOUNT = 0 THEN




    INSERT INTO CP_INDACC (CB_INDIVIDUAL_ACCTNO, CB_INDIVIDUAL_ACCT_TYPE, CB_FIN_ACCTNO, CB_LINE_LIMIT, CB_TEMP_LINE_LIMIT, CB_TEMP_START_DATE, CB_TEMP_END_DATE, CB_OUTSTD_BAL, CB_FEE_WAIVER_SPEND, CB_IND_CARDHOLDER_NO, CB_RSKPRF_ID, CB_SYNCDATE, CHECKSUM) VALUES (temp_CB_INDIVIDUAL_ACCTNO,temp_CB_INDIVIDUAL_ACCT _TYPE,temp_CB_FIN_ACCTNO,temp_CB_LINE_LIMIT,temp_C B_TEMP_LINE_LIMIT,temp_CB_TEMP_START_DATE,temp_CB_ TEMP_END_DATE,temp_CB_OUTSTD_BAL,temp_CB_FEE_WAIVE R_SPEND,temp_CB_IND_CARDHOLDER_NO,temp_CB_RSKPRF_I D,temp_CB_SYNCDATE,temp_CHECKSUM);

    END IF;

    --| var_count := SYNC_INTCAL_TBL(temp_CB_FIN_ACCTNO);



    Set var_count = SYNC_INTCAL_TBL;


    IF var_count = 1 THEN


    SET NUMVAR = 1;

    ELSE


    SET NUMVAR = 0;

    END IF;


    IF NUMVAR = 1 THEN

    --| var_count := SYNC_FINHST_TBL;



    set var_count = SYNC_FINHST_TBL;



    IF var_count = 1 THEN


    SET NUMVAR = 1;

    ELSE


    SET NUMVAR = 0;

    END IF;

    END IF;

    --| if NUMVAR = 1 THEN

    IF NUMVAR = 1 THEN

    --| var_count := SYNC_FINEXT_TBL;

    --*

    SET var_count = SYNC_FINEXT_TBL;

    --| if var_count = 1 THEN

    IF var_count = 1 THEN

    --| NUMVAR := 1;

    SET NUMVAR = 1;

    ELSE

    --| NUMVAR := 0;

    SET NUMVAR = 0;

    END IF;

    END IF;

    --| if NUMVAR = 1 THEN

    IF NUMVAR = 1 THEN

    --| var_count := SYNC_FINTBL_TBL(temp_CB_FIN_ACCTNO);

    --*

    SET var_count = SYNC_FINTBL_TBL;

    --| if var_count = 1 THEN

    IF var_count = 1 THEN

    --| NUMVAR := 1;

    SET NUMVAR = 1;

    ELSE

    --| NUMVAR := 0;

    SET NUMVAR = 0;

    END IF;

    END IF;

    END LOOP LOOP_LABEL;

    --| CLOSE cindacc;

    CLOSE cindacc;

    END;

    --| RETURN NUMVAR;

    SET RETURN_VAL = NUMVAR;
    RETURN 0;

    END@
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release and platform OS are you using?

    If you are not using latest DB2, there is a possibility of problem in parameter datatype.
    Try INOUT IN_CB_FIN_ACCTNO VARCHAR(20).


    Anyway, I thought the function-body might be replaced by a MERGE statement.

  3. #3
    Join Date
    Jan 2009
    Posts
    33

    need help with functions

    Hello ,
    I am using db2 v9.7 and currently testing this function on a windows machine running on db2 9.7 and I am not able to understand how to replace it with merge.

    Regards,
    Sharath

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sharaths_81 View Post
    Set var_count = SYNC_INTCAL_TBL;
    This function call seems to be missing a parameter. Functions are distinguished by the parameter count and type(s).

  5. #5
    Join Date
    Jan 2009
    Posts
    33

    need help with functions

    Hi,
    i am getting the same error even if i pass the parameters.
    just an excerpt of the function log file.


    var_count := SYNC_INTCAL_TBL(temp_CB_FIN_ACCTNO);
    if var_count = 1 THEN
    NUMVAR := 1;
    else
    NUMVAR := 0;
    end if;
    if NUMVAR = 1 THEN
    var_count := SYNC_FINHST_TBL(temp_CB_FIN_ACCTNO);
    if var_count = 1 THEN
    NUMVAR := 1;
    else
    NUMVAR := 0;
    end if;
    end if;
    if NUMVAR = 1 THEN
    var_count := SYNC_FINEXT_TBL(temp_CB_FIN_ACCTNO);
    if var_count = 1 THEN
    NUMVAR := 1;
    else
    NUMVAR := 0;
    end if;
    end if;
    if NUMVAR = 1 THEN
    var_count := SYNC_FINTBL_TBL(temp_CB_FIN_ACCTNO);
    if var_count = 1 THEN
    NUMVAR := 1;
    else
    NUMVAR := 0;
    end if;
    end if;
    END LOOP;
    CLOSE cindacc;

    EXCEPTION
    WHEN Others THEN
    NUMVAR := 0;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    CLOSE cindacc;
    END;
    RETURN NUMVAR;
    END;


    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0440N No authorized routine named "SYNC_INTCAL_TBL" of type "FUNCTION"having compatible arguments was found. LINE NUMBER=49. SQLSTATE=42884

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You declared two parameters for the function.
    But, gave one parameter at the time calling it.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are only 41 lines in your (sadly, unformatted) example. I can't see how the error can be on line 49.

  8. #8
    Join Date
    Jan 2009
    Posts
    33

    need help with functions

    Hello,
    Pls check the attachments. and let me know if that information is confusing.
    There is only one input value and one returns value.

    sorry for any confusions created.
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CREATE or REPLACE function SYNC_INTCAL_TBL ( INOUT IN_CB_FIN_ACCTNO CHAR(20),
    OUT RETURN_VAL DECIMAL(31,0))
    RETURNS INTEGER
    This means that you need to give two parameters.

    Your intension might be ...
    CREATE or REPLACE function SYNC_INTCAL_TBL ( INOUT IN_CB_FIN_ACCTNO CHAR(20) )
    RETURNS INTEGER
    ...


    Anyway, why you didn't you ask the value of var_count directly in the if clause?
    var_count := SYNC_INTCAL_TBL(temp_CB_FIN_ACCTNO);
    if var_count = 1 THEN
    NUMVAR := 1;
    else
    NUMVAR := 0;
    end if;
    if NUMVAR = 1 THEN
    var_count := SYNC_FINHST_TBL(temp_CB_FIN_ACCTNO);
    ...
    like
    var_count := SYNC_INTCAL_TBL(temp_CB_FIN_ACCTNO);
    if var_count = 1 THEN
    var_count := SYNC_FINHST_TBL(temp_CB_FIN_ACCTNO);
    ...

  10. #10
    Join Date
    Aug 2011
    Posts
    45
    r u calling function from the ID created that function..if not then add qualifier to the function name...
    e.g. db2inst.func_name

  11. #11
    Join Date
    Jan 2009
    Posts
    33

    need help with functions

    but first of all the issue in my case is it is not calling teh function itself teh if clause i have to bother abt after the function is called successfully. And Harsh i am calling it from the same user as the user created.

    Regards,
    Sharath

Posting Permissions

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