Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: Dynamic Procedure giving Error

    Hi,

    I have created my dynamic procedure in which values are passed at the run time and on that basis query is executed. But i am getting error.

    Please help me out.
    Error :>
    ERROR [42884] [IBM][DB2/NT] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=19. SQLSTATE=42884

    ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++
    CREATE PROCEDURE DB2ADMIN.Dup_Data_1 (
    IN name_business_in varchar(100),
    IN corp_id_in integer )
    P1: BEGIN
    DECLARE chk_val INT DEFAULT 0;
    DECLARE v_name_business varchar(100);
    DECLARE v_corp_id integer;
    DECLARE v_stmt varchar(2000);

    DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;


    SET v_stmt = ' n.NAME_BUSINESS,
    c.CORP_ID
    FROM
    CLI_CLIENT c
    JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID WHERE ';

    IF name_business_in is not null then
    set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
    END IF;
    IF corp_id_in is not null then
    set v_stmt = v_stmt || 'corp_id = ' || '''' || corp_id_in || '''';
    END IF;

    PREPARE s1 FROM v_stmt;
    open sr;
    FETCH sr INTO
    v_client_id,v_name_business,v_corp_id;

    IF chk_val = 1 THEN
    INSERT INTO cli_clnt_duplicate_test
    VALUES(v_name_business,v_corp_id);
    END IF;

    CLOSE sr;
    END P1
    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I already answered that via email: concatenating strings with non-strings is not supported. (SQL is strongly typed, after all.) One has to convert the integer CORP_ID_IN to a string first.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Oct 2007
    Posts
    5
    Yes i got that....I had put it here and then i mailed you....I am facing new error now.....

    ERROR [42802] [IBM][DB2/NT] SQL0117N The number of values assigned is not the same as the number of specified or implied columns. LINE NUMBER=30. SQLSTATE=42802

    Please help me out....

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    I think it's this line, that is in error:

    INSERT INTO cli_clnt_duplicate_test
    VALUES(v_name_business,v_corp_id);

    how many columns does that table have ?

  5. #5
    Join Date
    Oct 2007
    Posts
    5
    Infact the table has more columns than i am inserting and none of the columns is not null so that i can insert data in as many columns as i want.

    What should i do.....

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    you have to specify the column names, to let DB2 know which of the columns should be filled with data:

    INSERT INTO cli_clnt_duplicate_test ( column1, column2 )
    VALUES ( value1 , value2 ) ;

  7. #7
    Join Date
    Oct 2007
    Posts
    5
    Thank you its working now.....It was very silly mistake which i should had identified. Any way thanks a lot.

Posting Permissions

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