Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: Simple addition is not working

    Hi Friends,

    I am new to DB2. I write the simplefor.sql(see attached file) stored procedure. It simply fetches the 'dept' number of all the rows (using for loop) from the staff table and adds to the output parameter 'dept1'. The stored procedure gets successfully created into the SAMPLE database.
    When I call the stored procedure using
    'db2 CALL SIMPLEFOR\(?\)', the output is displayed as shown below:

    Value of output parameters
    --------------------------
    Parameter Name : DEPT1
    Parameter Value : -

    Return Status = 0

    See, the value is not printed for 'dept1' output parameter.

    My M/C: Linux 8.0
    My DB2: UDB/DB2 v8.1

    Please let me know where I am going wrong or is it DB2's bug?

    Thanks & Regards,
    Jake
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Jake,
    The reason is that you never initialized dept1. It will be intiialized to NULL. And then you can probably guess that addition with a null will result in null. Try it this way:


    CREATE PROCEDURE SIMPLEFOR(OUT dept1 INT)
    LANGUAGE SQL
    BEGIN
    SET dept1 = 0;
    FOR v2 AS
    SELECT dept FROM staff
    DO
    SET dept1 = dept1 + dept;
    END FOR ;
    END
    @


    HTH

    Andy

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    Hi Andy,

    Thanks for the help. You are correct. It's working fine after I initialized the output parameter.

    Thanks & Regards,
    Jake

    Originally posted by ARWinner
    Jake,
    The reason is that you never initialized dept1. It will be intiialized to NULL. And then you can probably guess that addition with a null will result in null. Try it this way:


    CREATE PROCEDURE SIMPLEFOR(OUT dept1 INT)
    LANGUAGE SQL
    BEGIN
    SET dept1 = 0;
    FOR v2 AS
    SELECT dept FROM staff
    DO
    SET dept1 = dept1 + dept;
    END FOR ;
    END
    @


    HTH

    Andy

Posting Permissions

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