# Thread: Comparing two tables and computing percentage change

1. Registered User
Join Date
Jul 2009
Posts
6

## Unanswered: Comparing two tables and computing percentage change

Hi,

I'm a novice to sql scripting. Am trying to figure out a design problem, involving some arithmatic computation.

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

1. I want to compare the balance feilds in temp1 and temp2.
print out the no. of accounts and percentage of match and mismatch.
2. output the a/c nos. whose balances dont match into a seperate 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

2. Registered User
Join Date
Jun 2004
Posts
812
Provided Answers: 1
In what way is what you are trying to achieve a 'design problem'?

3. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
Provided Answers: 5
Originally Posted by novice82
how does sql handle the computation, if a value in a particular field is divided by 0 ?
In a way that it raises an exception (what did you expect?). Which one? Try and see; it is easy, doesn't cost much, needs only SQL*Plus.

4. Registered User
Join Date
Jul 2009
Posts
6
I meant to ask, how the " divisor is equal to zero " exception can be handled.. There is no need to be sarcastic with someone whose trying to learn the ropes and jump into conclusions on your own.

5. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
Provided Answers: 5
Well, how was I supposed to know what you mean? All I can see is what you've written, and it says "how does Oracle handle division by zero?" - it raises an exception. "How would YOU handle division by zero?" is something different. Here's a way or two which might help.

In PL/SQL, you'd write an exception handler:
Code:
```SQL> declare
2    l_var number;
3  begin
4    l_var := 2 / 0;
5
6    dbms_output.put_line('2 / 0 = ' || l_var);
7  exception
8    when zero_divide then
9      null;
10      dbms_output.put_line('2 / 0 = Error');
11  end;
12  /
2 / 0 = Error

PL/SQL procedure successfully completed.

SQL>```
It is up to you what you'll do with that; "NULL;" command will do nothing (i.e. you'll ignore it). Or, you may do something different.

For example, if a value "small enough" is good enough for you, here's one way to do that (this time SQL example):
Code:
```SQL> select 2 / decode(&&possible_zero, 0, 1E99, &&possible_zero) result from dual;
Enter value for possible_zero: 0

RESULT
----------
2.0000E-99

SQL>```
A better way is probably dividing by NULL (as the result will be NULL):
Code:
```SQL> select 2 / decode(&&possible_zero, 0, null, &&possible_zero) result from dual;
Enter value for zero: 0

RESULT
----------

SQL>```
Now it is up to you; there are several options, and you may choose one of them. The question is: how would you like to handle this exception? What would you like to get as a result?

6. Registered User
Join Date
Jul 2009
Posts
6
Thanks mate.

PL/SQL is not my cup of tea. However, I have found equivalent solutions with basic sql querys.

Thanks for helping out !

7. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
Provided Answers: 1
>However, I have found equivalent solutions with basic sql querys.
Please post your solution so it is available to others.

8. Registered User
Join Date
Jul 2009
Posts
6
SELECT temp1.account_no, temp1.balance,
CASE WHEN temp1.balance = temp2.balance THEN 0.0
ELSE ABS(temp1.balance = temp2.balance) / GREATEST(temp1.balance, temp2.balance)
END
FROM temp1
JOIN temp2 ON temp1.account_no = temp2.account_no

9. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
Provided Answers: 1
The Quality Assurance folks need to be (re)trained, if fesible.
Code:
```SQL> @id1
SQL> set term on echo on
SQL> drop table temp1;

Table dropped.

SQL> drop table temp2;

Table dropped.

SQL> create table temp1 (account_no number, balance float);

Table created.

SQL> create table temp2 (account_no number, balance float);

Table created.

SQL> insert into temp1 values(1,1.5);

1 row created.

SQL> insert into temp1 values(2,2.5);

1 row created.

SQL> insert into temp2 values(1,1.5);

1 row created.

SQL> insert into temp2 values(2,3.5);

1 row created.

SQL> commit;

Commit complete.

SQL> --
SQL> SELECT temp1.account_no, temp1.balance,
2  CASE WHEN temp1.balance = temp2.balance THEN 0.0
3  ELSE ABS(temp1.balance = temp2.balance) / GREATEST(temp1.balance, temp2.balance)
4  END
5  FROM temp1
6  JOIN temp2 ON temp1.account_no = temp2.account_no;
ELSE ABS(temp1.balance = temp2.balance) / GREATEST(temp1.balance, temp2.balance)
*
ERROR at line 3:
ORA-00907: missing right parenthesis```
The wonderous thing about SQL it will ALWAYS return a result set (except when a syntax error intervenes).
Whether or not the result set is "correct", is for the QA department to verify.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•