Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Loosing Number Precision in Union

    Hey,

    I have created a sql statement with a union but I am loosing my number precision for one of my columns and I can't figure out why. Here is what I am doing:

    select
    (a+b+c+d) where each of these are number(5,2)
    union
    select
    (e+f+g+h) where each of these are number(5,2)

    but the result is a value with no precision, I am loosing my decimals when using the union. The selects work fine separately.

    Any ideas?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    Not possible unless the result of each sum has no decimals:
    Code:
    SQL>select (1.1+1.2+1.3) from dual
      2  union
      3  select (2.1+3.2+3.3) from dual;
    
    (1.1+1.2+1.3)
    -------------
              3.6
              8.6
    SQL>select (1.1+1.2+1.7) from dual
      2  union
      3* select (2.5+3.2+3.3) from dual;
    
    (1.1+1.2+1.7)
    -------------
                4
                9


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If the numbers (a+b+c+d) add up to a whole number, then you would not see a decimal point. If you always want to see the decimal portion, then type

    set numf 99999.99


    to set the default number format. (make sure you have a big enough format to handle your biggest number)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2004
    Posts
    74
    Thanks for the replies.

    I though so as well. So I went back and added up the sums manually, and there are decimals. What is happening seems very weird to me, so I created a view from my sql statement to see the data types of the created columns and the column containig the sum of the number(5,2)'s is now just number with no precision.

    I just don't know what is happening.

Posting Permissions

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