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

novice82 escribió:
It raises a ORA-01476 error ("divisor is equal to zero"). You can test
it yourself:

SELECT 1/0
FROM DUAL


--
-- 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
--

On Jul 20, 4:35*am, novice82 <novic... (AT) gmail (DOT) com> wrote:
the first thing you need to type is:

SELECT



Now really, the question is what have you tried?
Exactly what knowledge of SQL do you have?
(I have seen a SQL for Dummies book,
I've actually opened a SQL for Dummies book,
I have read a SQL for Dummies book,
I have seen the Oracle web page,
I have seen the Oracle SQL web pages,
I have read some of the Oracle SQL web pages, ...)

We do not have the time or space to give a course in SQL in the
newsgroup. Help us help you.

Ed

On Jul 20, 9:40*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
Try something. You might want to search the docs at tahiti.oracle.com
for the outer join syntax to account for the case of missing rows.

When you figure out the syntax, you can do something like create table
dontmatch as select <some fields> from <what you figured out>

What are the business rules for such a situation?

JEG@TTST> select 1/0 from dual;
select 1/0 from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero


JEG@TTST> select 1/decode('0',0,'100') from dual;

1/DECODE('0',0,'100')
---------------------
.01

LOL! Oracle Purity test!

jg
--
@home.com is bogus.
http://www.bofh.net/sl_Purity.html

On Jul 20, 5:14*pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
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.

Any help, will be appreciated.

thanks

novice82 escribió:

You have to fix it in all the divisions, not only the SELECT clause.





--
-- 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
--

Álvaro G. Vicario escribió:
.... 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
--

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

On Jul 21, 5:28*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
Thanks a lot.

That works like a charm!