Re: Computing percentage change between tables

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Tue, 21 Jul 2009 11:19:32 +0200
Message-ID: <h441jb$mnt$2_at_news.eternal-september.org>



Álvaro G. Vicario escribió:
> novice82 escribió:

>> On Jul 20, 5:14 pm, "Álvaro G. Vicario"
>> <alvaro.NOSPAMTH..._at_demogracia.com.invalid> wrote:
>>> novice82 escribió:
>>>
>>>> another question, that I have is, how does sql handle the computation,
>>>> if a value in a particular field is divided by 0 ?
>>> It raises a ORA-01476 error ("divisor is equal to zero"). You can test
>>> it yourself:
>>>
>>> SELECT 1/0
>>> FROM DUAL
> 
> 

>> I tried the following :
>>
>> SELECT T1.AcountNo, T1.Balance, T2.Balance, T1.Balance/CASE T2.Balance
>> WHEN 0 THEN NULL END)
>> FROM Temp1 T1, Temp2 T2
>> WHERE T1.AccountNo = T2.AccountNo
>> AND ((T1.Balance / T2.Balance > 1.1)
>> OR (T1.Balance / T2.Balance < 0.9))
>>
>> But i'm still unable to circumvent the zero divisor problem.
>
> You have to fix it in all the divisions, not only the SELECT clause.

... though in this case I suppose you could just remove the rows where T2.Balance is zero; after all, the other conditions filter them out anyway:

WHERE T1.AccountNo = T2.AccountNo
AND T2.Balance<>0
AND ((T1.Balance / T2.Balance > 1.1)
OR (T1.Balance / T2.Balance < 0.9))

-- 
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
Received on Tue Jul 21 2009 - 04:19:32 CDT

Original text of this message