How to replace 0

heyit · #1 (**permalink**) 07-21-10, 12:02

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!!

n_i · #2 (**permalink**) 07-21-10, 12:18

replace C_QTY with "case C_QTY when 0 then 1 else C_QTY end"

tonkuma · #3 (**permalink**) 07-21-10, 15:58

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.

Lenny77 · #4 (**permalink**) 07-21-10, 18:04

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

umayer · #5 (**permalink**) 07-22-10, 01:48

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

Lenny77 · #6 (**permalink**) 07-22-10, 11:04

Quote:

...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

Internet Services

Web Development

Digital Marketing

Consumer Tech