Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    12

    Unanswered: How to replace 0

    I'm 100% new to DB2. We have a bug in code where one returning value making total result incorrectly '0' (zero).

    Here is the statement which we are executing:

    SELECT SUM(MULTIPLY_ALT(A_QTY, B_QTY, C_QTY)) from CUST_QUANTITY;

    I believe this statement is multiplying A_QTY, B_QTY, C_QTY for each row and later adding all the rows (please correct me if I'm not).

    How do I check if C_QTY is not 0 (zero) and if it is 0, treat it as 1 so that multiplication of (A_QTY, B_QTY, C_QTY) should not become 0.

    I need to do this inside the select statement only.

    Please help.

    Thx!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    replace C_QTY with "case C_QTY when 0 then 1 else C_QTY end"

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although it is not a subject of this topic, are more than three parameters for MULTIPLY_ALT supported?
    MULTIPLY_ALT(A_QTY, B_QTY, C_QTY)

    I couldn't find that support on manuals(DB2 for LUW, z/OS and iSeries).
    Also, I tried on DB2 9.7.2 on Windows and failed:
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES MULTIPLY_ALT(1 , 2 , 3);
    ------------------------------------------------------------------------------
    SQL0440N  No authorized routine named "MULTIPLY_ALT" of type "FUNCTION" having 
    compatible arguments was found.  SQLSTATE=42884
    
    ------------------------------ Commands Entered ------------------------------
    VALUES MULTIPLY_ALT(1 , 2);
    ------------------------------------------------------------------------------
    
    1                       
    ------------------------
                          2.
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    VALUES MULTIPLY_ALT( MULTIPLY_ALT(1 , 2) , 3 );
    ------------------------------------------------------------------------------
    
    1                                
    ---------------------------------
                                   6.
    
      1 record(s) selected.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Why you have to use the function, if simple multiplication working much faster ?
    Check this query (no case was used):


    Code:
    SELECT SUM( A_QTY * B_QTY * ifnull( nullif(C_QTY, 0),  1) ) 
    from CUST_QUANTITY;
    Lenny

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    Lenny77's query may return an incorrect result if C_QTY is nullable, as each occurence of a NULL-value will be replaced by 1

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb umayer, you are right. It was just an idea.

    ...multiplication of (A_QTY, B_QTY, C_QTY) should not become 0...
    So

    Code:
    select SUM(case when  A_QTY * B_QTY * C_QTY = 0 then 1 
                    else  A_QTY * B_QTY * C_QTY
               end )
    from CUST_QUANTITY;
    will be a query we are looking for

    Lenny

Tags for this Thread

Posting Permissions

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