Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Unanswered: Using Sum function in a stored procedure

    I am trying to use either of the following SELECT statements in a stored procedure. The purpose of the select is to obtain the sum of (E06_BUDGETED_UNITS * E07_PDD_SVS_RATE) for all rows where E06_SERVICES_ID <> SERVICESID_0. I should be getting a result of $36,892.00 but instead receive a null value when testing (using stored procedure builder).

    With the test data I'm using, valid values for E06_SERVICES_ID are 156897,156898,156899, & 156900.

    Is what I'm trying to do even possible since it involves more than one row?


    SELECT SUM(E06_BUDGETED_UNITS * E07_PDD_SVS_RATE) INTO AMOUNT
    -- FROM SERDB.E06_SERVICES_T
    FROM SERDB.TEMPE06_T
    INNER JOIN SERDB.E07_PDD_SVS_RATE_T
    ON E07_RATE_CHG_ID = E06_RATE_CHG_ID
    INNER JOIN SERDB.E05_CONS_BUDGET_T
    ON E05_CONSUMER_ID = E06_CONSUMER_ID
    AND E05_BUDGET_ID = E06_BUDGET_ID
    WHERE E06_BUDGET_ID = BUDGETID_0
    AND E06_CONSUMER_ID = CONSUMERID_0
    AND E06_SERVICES_ID <> SERVICESID_0;

    Or

    SELECT SUM(E06_BUDGETED_UNITS * E07_PDD_SVS_RATE)
    INTO AMOUNT
    -- FROM SERDB.E06_SERVICES_T
    FROM SERDB.E05_CONS_BUDGET_T,
    SERDB.TEMPE06_T,
    SERDB.E07_PDD_SVS_RATE_T
    WHERE E07_RATE_CHG_ID = E06_RATE_CHG_ID
    AND E05_CONSUMER_ID = E06_CONSUMER_ID
    AND E05_BUDGET_ID = E06_BUDGET_ID
    AND E06_BUDGET_ID = BUDGETID_0
    AND E06_CONSUMER_ID = CONSUMERID_0
    AND E06_SERVICES_ID <> SERVICESID_0;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You obviously have null values in the E06_BUDGETED_UNITS and/or E07_PDD_SVS_RATE columns, which makes the result of SUM() null as well. Use COALESCE to deal with the null values.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    ... or filter-out those rows with a predicate in the WHERE clause.

    Also, there is nothing special about your query. For debugging purposes, you may want to build up the query step-by-step using the command line or command editor and verify the results on each step.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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