如何在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:

我将磁盘容量存储在2列中,如CAPACITY_TOTAL和CAPACITY_USED。我需要提供%利用率,我需要将CAPACITY_USED除以CAPACITY_USED / CAPACITY_TOTAL * 100的CAPACITY_USED。我使用下面的查询:

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.

我已经将我的列转换为十进制,但即使这样也行不通。

2 个解决方案

#1

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.

好的,所以SQLSTATE = 22012表示你试图除以零。显然,您的某些行在CAPACITY_TOTAL列中包含空值或零,并且db无法计算公式。

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:

或者,跳过CAPACITY_TOTAL为null或零的行:

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

#2

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

错误代码801表示您的查询试图除以零。

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

#1