Re: Computing percentage change between tables
From: novice82 <novice82_at_gmail.com>
Date: Wed, 22 Jul 2009 18:13:20 -0700 (PDT)
Message-ID: <4fd70be9-e913-4c76-9e14-9adba64d10b6_at_v15g2000prn.googlegroups.com>
On Jul 21, 5:28 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Á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
Date: Wed, 22 Jul 2009 18:13:20 -0700 (PDT)
Message-ID: <4fd70be9-e913-4c76-9e14-9adba64d10b6_at_v15g2000prn.googlegroups.com>
On Jul 21, 5:28 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Á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
Thanks a lot.
That works like a charm! Received on Wed Jul 22 2009 - 20:13:20 CDT