Computing percentage change between tables

Question

Hi,

I'm a novice to SQL scripting. I am trying to figure out a design problem, involving some arithmetic computation.

I have two tables temp1 and temp2 with two columns account no., ( Common in both tables ) balance ( float data type ).

1. I want to compare the balance columns in temp1 and temp2. print out the no. of accounts and percentage of match and mismatch.
2. output the a/c nos. whose balances don't match into a separate table..

Another question that I have is, how does sql handle the computation, if a value in a particular field is divided by 0?

Any help, will get me started.

Thanks

Answer 1

For the first query I would use something like this, which prints the ratio of the two balances:

SELECT T1.AcountNo, T1.Balance, T2.Balance, T1.Balance / T2.Balance
FROM Temp1 T1, Temp2 T2
WHERE 
T1.AccountNo = T2.AccountNo

For the second query, I would use something like this, that returns all the accounts for which the different is more than 10%:

SELECT T1.AcountNo, T1.Balance, T2.Balance, T1.Balance / T2.Balance
FROM Temp1 T1, Temp2 T2
WHERE 
T1.AccountNo = T2.AccountNo
AND 
((T1.Balance / T2.Balance > 1.1)
OR
(T1.Balance / T2.Balance < 0.9))

Answer 2

Thanks Rax,

But how do i handle the zero divisor error ? I have read numerous posts, but hav'ent been able to draw any conclusions..

Answer 3

Hi,

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.

Answer 4

SELECT T1.AcountNo, T1.Balance, T2.Balance, 
       DECODE( NVL(T2.Balance, 0), 0, 0, T1.Balance, T2.Balance)
FROM Temp1 T1, Temp2 T2 
WHERE T1.AccountNo = T2.AccountNo 
  AND ((T1.Balance / T2.Balance > 1.1) OR (T1.Balance / T2.Balance < 0.9))

˙DECODE` function is defined like this:

decode( expression , search , result [, search , result]... [, default] )

So, in above code it works like this:

• NVL() - if T2.Balance is NULL convert it to 0
• if T2.Balance is 0, return 0
• else divide T1.Balance with T2.Balance

Answer 5

In additions to the suggestions above (which are good) there's also this visual tool that lets you see differences in the resulting SQLs - Columbo