Erro Divisão por Zero (Arithmetic overflow or division...)
Senhores,
Como posso solucionar o erro de Divisão por zero (Arithmetic overflow or division by zero) no código abaixo, uma vez que eu preciso que retorne 0 quando a divisão do campo pela quantidade for zero ou nula?
Estou usando a versão 2.0.1 do Firebird
SELECT 5 AS ID, 2 AS ITEM, ´Angiologia´ AS DESCRICAO,
(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS QTD,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 1)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS OTIMO,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 2)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS BOM,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 3)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS REGULAR,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 4)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS RUIM
FROM AMBULATORIO
GROUP BY 1 UNION
SELECT 6 AS ID, 2 AS ITEM, ´Cabeça e Pescoço´ AS DESCRICAO,
(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS QTD,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 1)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS OTIMO,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 2)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS BOM,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 3)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS REGULAR,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 4)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS RUIM
FROM AMBULATORIO
GROUP BY 2
Obrigado a todos que ajudarem.
Como posso solucionar o erro de Divisão por zero (Arithmetic overflow or division by zero) no código abaixo, uma vez que eu preciso que retorne 0 quando a divisão do campo pela quantidade for zero ou nula?
Estou usando a versão 2.0.1 do Firebird
SELECT 5 AS ID, 2 AS ITEM, ´Angiologia´ AS DESCRICAO,
(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS QTD,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 1)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS OTIMO,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 2)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS BOM,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 3)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS REGULAR,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA = 4)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.ANGIOLOGIA <> 0) AS RUIM
FROM AMBULATORIO
GROUP BY 1 UNION
SELECT 6 AS ID, 2 AS ITEM, ´Cabeça e Pescoço´ AS DESCRICAO,
(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS QTD,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 1)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS OTIMO,
100*(SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 2)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS BOM,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 3)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS REGULAR,
100* (SELECT COUNT(*) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO = 4)/
(SELECT CAST(COUNT(*) as double precision) FROM AMBULATORIO WHERE AMBULATORIO.CABECAPESCOCO <> 0) AS RUIM
FROM AMBULATORIO
GROUP BY 2
Obrigado a todos que ajudarem.
Belo
Curtidas 0
Respostas
Sremulador
26/03/2007
utilize o coalesce (campo, valor caso nulo)
GOSTEI 0