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

    Unanswered: Snapshot number column precision

    Hey,

    I have created a snapshot on the database but I am not getting the correct precision on one of the columns that is needed. The select statement is like this "select....., sum(A+B+C+D) as Total,..." where A, B, C, D are number columns defined as number(5,2) on the table in the database but when the snapshot is created the precision is different then as defined by each number, its now just number, with no precision. Does anyone know how I can maintain the precision on Total when the snapshot is created?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I think that you shouldn't be worried about it; NUMBER datatype (with no precision and scale specified) is a floating point number with precision 38. A floating point value either can have a decimal point anywhere from the first to the last digit or can omit the decimal point. A scale value is not applicable to floating point numbers because there is no restriction on the number of digits that can appear after the decimal point.

    Therefore, all values stored in "original" table can easily be reproduced in materialized view which is based upon this table; now you have even greater flexibility (which you can't use, but nevertheless).

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Interesting - I thought you might be able to do this with CAST, but apparently not:

    Code:
    SQL> CREATE TABLE test1 (n1 NUMBER(5,2), n2 NUMBER(5,2));
    
    Table created.
    
    SQL> CREATE TABLE test2 AS SELECT n1 + n2 n FROM test1;
    
    Table created.
    
    SQL> set lines 60
    
    SQL> desc test2
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     N                                      NUMBER
    
    
    SQL> CREATE TABLE test2 AS SELECT CAST(n1 + n2 AS NUMBER(5,2)) n FROM test1;
    
    Table created.
    
    SQL> desc test2
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     N                                      NUMBER
    Perhaps you could create the table explicitly, then create the MV using the ON PREBUILT TABLE clause.

    btw the term "snapshot" is deprecated in favour of "materialized view".

Posting Permissions

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