Results 1 to 9 of 9
  1. #1
    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. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    In what way is what you are trying to achieve a 'design problem'?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote 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. #4
    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. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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. #6
    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. #7
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    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. #9
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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