Re: Problems with rounding

On Nov 22, 4:49 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi Everyone,
>
> I'm beginning to use external tables. I put some character data
> into the table,. It was a signed number, such as -123456.187. When I
> select the data using this select statement, it seems to round the
> data automatically. Why is this? I must be doing something
> wrong.........
>
> Thanks in advance for your help.
>
> ACTUAL DATA: -123456.187
>
> SQL> select to_number(field1,'999999999.999') from art_ext;
>
> TO_NUMBER(FIELD1,'999999999.999')
> ---------------------------------
> -1234567.2
>
> CREATE TABLE art_ext (
> FIELD1 VARCHAR2(20))
> Organization external
> (type oracle_loader
> default directory indata_directory
> access parameters (records delimited by newline
> fields terminated by ',')
> location ('art.txt'))
> reject limit 1000;

You are converting a varchar2 to a number, and this number is subsequently implicitly converted to a varchar2 using the default display format.

select to_char(to_number(-123456.187,'999999999.999'),'999999999.999') from dual
/

TO_CHAR(TO_NUM

   -123456.187

Your statement results in my database in TO_NUMBER(-123456.187,'999999999.999')

                            -123456.19

--
Sybrand Bakker
Senior Oracle DBA