Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Question Unanswered: Numeric precision conversion : truncation instead of rounding

    I'm working in a project of converting an application from Syabse to
    Oracle (1000 man-day).
    In Sybase, the result of convert(numeric(4,2),1.2378) is 1.23
    In Oracle, the result of cast(1.2378 as number(4,2)) is 1.24
    I look the same thing in implicit conversion : insert number(6,4) value
    in a table column of the type numeric(4,2)
    How to modify the oracle behaviour (any server option ? )to do
    truncation instead of rounding ? Of course I can use truncate function
    but it will complicate all the obvious conversions.
    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    truncate function will complicate all the obvious conversions
    Why wouldn't you use a TRUNC function if it does exactly what you need? In my opinion (and in your question), use of this function is more obvious than using CAST function. CAST converts one built-in datatype into another built-in datatype. 1.2378 already IS a number - there's no need to convert it into number again.

  3. #3
    Join Date
    Oct 2005
    Posts
    1
    try out this
    trunc((cast(1.2378 as number(6,4))),2)

  4. #4
    Join Date
    Oct 2005
    Posts
    5

    Why TRUNC isn't obvious

    I have some thing like 100 000 diffrent sql request. 90% of them are a simple request. For exapmle : "create tab2 as select * from tab1". The conversion of this one is automatically and don't needs any thing special. If I use TRUNC function, I must rewrite this request ! So, what was very obvious eand easy will be complicated and tiresome.

    Quote Originally Posted by Littlefoot
    Why wouldn't you use a TRUNC function if it does exactly what you need? In my opinion (and in your question), use of this function is more obvious than using CAST function. CAST converts one built-in datatype into another built-in datatype. 1.2378 already IS a number - there's no need to convert it into number again.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is your target column datatype? Is it already "prepared" to accept formatted input (let's say, NUMBER(4, 2))? If columns are of a NUMBER datatype, without precision, you might insert values as they are, for example

    INSERT INTO your_table (column_name) VALUES (1.2378);

    This would be the easy part (your simple request. Later, when all the data is in Oracle tables, you could truncate this value as

    UPDATE your_table SET column_name = TRUNC(column_name, 2);

    However, it would not be possible to redesign table columns using

    ALTER TABLE your_table MODIFY column_name NUMBER(4, 2);

    as you'd run into ORA-01440: column to be modified must be empty to decrease precision or scale.

    Or, as the seconed idea, you could first prepare data in Sybase and move "clean" data into Oracle tables.

    Or, as my last idea, you could create a database trigger on Oracle tables which will perform desired truncation, such as

    CREATE OR REPLACE trg_truncate
    BEFORE INSERT ON your_table
    FOR EACH ROW
    BEGIN
    :new.column_name := TRUNCATE(:new.column_name, 2);
    END;

    This will, however (at least I thinks so), slow down the process (inserting data without trigger would be faster than with the trigger), especially when dealing with large data set.

    I agree that setting Oracle database to truncate numbers instead of rounding them would solve such problems, but I don't know how to do that.

Posting Permissions

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