CODEWITHSUNDEEP
sqldb2
andruX
andruX

Reputation: 117

How to Divide two columns in DB2 SQL

I have disk capacities stored in 2 columns as CAPACITY_TOTAL and CAPACITY_USED. I need to present % utilization for which I need to divide CAPACITY_USED by CAPACITY_TOTAL as CAPACITY_USED/CAPACITY_TOTAL * 100. I am using below Query:

select CAPACITY_USED / CAPACITY_TOTAL from TableName

which gives below error:

DB2 SQL Error: SQLCODE=-801, SQLSTATE=22012, SQLERRMC=null, DRIVER=4.15.82

I have casted my columns to decimal but even that doesn't work.

Upvotes: 2

Views: 15772

Answers (2)

npe
npe

Reputation: 15709

OK, so SQLSTATE=22012 means you're trying to divide by zero. Apparently some of your rows contain nulls or zeroes in CAPACITY_TOTAL column and db fails to calculate the formula.

Try this query for better results:

SELECT CASE CAPACITY_TOTAL 
            WHEN 0    THEN 0
            WHEN NULL THEN 0
            ELSE CAPACITY_USED / CAPACITY_TOTAL
       END AS UTILIZATION
  FROM MY_TABLE

You may want to put some other meaningful return values for zeroes / nulls.

Alternatively, skip rows, where CAPACITY_TOTAL is null or zero:

SELECT CAPACITY_USED / CAPACITY_TOTAL AS UTILIZATION
  FROM MY_TABLE
 WHERE CAPACITY_TOTAL IS NOT NULL
   AND CAPACITY_TOTAL > 0

Upvotes: 2

Pigasus
Pigasus

Reputation: 130

Error code 801 means your query was trying to divide by zero.

select CAPACITY_USED / CAPACITY_TOTAL from TableName
where CAPACITY_TOTAL IS NOT NULL and CAPACITY_TOTAL <> 0

Upvotes: 3

Related Questions