Re: Computing percentage change between tables

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 21 Jul 2009 11:28:20 +0200
Message-ID: <4A658A34.6080807_at_gmail.com>



┴lvaro G. Vicario schrieb:
> ┴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))
>
>
>

That would not necessarily eliminate the division exception due to arbitrary evaluation order of predicates. Better imho is to eliminate division itself

and (t1.balance > 1.1 * t2.balance ...

Best regards

Maxim

-- 
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?

Aksel Peter J°rgensen
Received on Tue Jul 21 2009 - 04:28:20 CDT

Original text of this message