sql loader rounding of decimals while loading--Urgent.Help!!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: sql loader rounding of decimals while loading--Urgent.Help!!!!

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Unhappy

    Hi,
    Here is the sample data which I am trying to load using sql loader.

    ALLEP ,1996,REGROLL ,ISTUSPI , 43522.886393, 34635.613098,YR
    ALLEP ,1996,REGROLL ,ISOCS , 11489.679547, 10619.922145,YR
    ALLEP ,1996,REGROLL ,ISNGS , 8335.460687, 8144.415170,YR
    ALLEP ,1996,REGROLL ,ISNGLS , 462.127086, 424.456126,YR


    Here is the control file I am using to load this data

    load data
    infile 'testa'
    badfile 'ofasweb.bad'
    discardfile 'ofasweb.dcs'
    truncate
    into table testme
    fields terminated by ","
    (company,
    time,
    region,
    lineitem,
    actual
    nullif (actual = BLANKS),
    actualus
    nullif (actualus = BLANKS),
    year_quarter)

    and here is what it loads
    COMPANY TIME REGION LINEITEM ACTUAL ACTUALUS YEAR_
    ---------- ---------- ---------- --------------- ---------- ---------- -----
    ALLEP 1996 REGROLL ISTUSPI 43522.8864 34635.6131 YR
    ALLEP 1996 REGROLL ISOCS 11489.6795 10619.9221 YR
    ALLEP 1996 REGROLL ISNGS 8335.46069 8144.41517 YR
    ALLEP 1996 REGROLL ISNGLS 462.127086 424.456126 YR

    Notice that the fields in bold have been rounded off.

    I dont want to do this since this is a financial database.

    Please suggest how.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How is your table defined?
    Are you sure you are displaying all digits after the decimal?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    Here is the table definition.
    As you can see that the actual and actualus columns have a decimal size of 6. But after loading it has only 4 decimals



    SQL> desc testme
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    COMPANY VARCHAR2(10)
    TIME VARCHAR2(10)
    REGION VARCHAR2(10)
    LINEITEM VARCHAR2(15)
    ACTUAL NUMBER(20,6)
    ACTUALUS NUMBER(20,6)
    YEAR_QUARTER VARCHAR2(5)

    Also if you see my last sample record in the previous post it is getting converted properly. Its only a few records.


    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  4. #4
    Join Date
    Jul 2000
    Posts
    243
    Hi

    i'v used sqlloader many times and never ren into this one, but the diff between you and me is that i never ren into a culomn defind as NUMBER(20,6).
    try
    ACTUAL NUMBER
    ACTUALUS NUMBER

    and see what happens

  5. #5
    Join Date
    Sep 2000
    Posts
    362
    Originally posted by shawish_sababa
    Hi

    i'v used sqlloader many times and never ren into this one, but the diff between you and me is that i never ren into a culomn defind as NUMBER(20,6).
    try
    ACTUAL NUMBER
    ACTUALUS NUMBER

    and see what happens
    Hi,
    I tried that also and still get the same result. It is very very important for me to preserve the decimals.


    SQL> desc testme
    Name Null? Type
    ----------------------------------------- -------- -------------------------
    COMPANY VARCHAR2(10)
    TIME VARCHAR2(10)
    REGION VARCHAR2(10)
    LINEITEM VARCHAR2(15)
    ACTUAL NUMBER
    ACTUALUS NUMBER
    YEAR_QUARTER VARCHAR2(5)

    SQL> select * from testme;

    COMPANY TIME REGION LINEITEM ACTUAL ACTUALUS YEAR_
    ---------- ---------- ---------- --------------- ---------- ---------- -----
    ALLEP 1996 REGROLL ISTUSPI 43522.8864 34635.6131 YR
    ALLEP 1996 REGROLL ISOCS 11489.6795 10619.9221 YR
    ALLEP 1996 REGROLL ISNGS 8335.46069 8144.41517 YR
    ALLEP 1996 REGROLL ISNGLS 462.127086 424.456126 YR

    Please suggest.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  6. #6
    Join Date
    Sep 2000
    Posts
    362
    Hi All,
    I was able to fix the problem from help with Oracle support. This is what they said.

    "the number itself is not being truncated, but the default column width for Number is set to 10. From SQL*Plus, use the command SET NUM nn where nn is the desired column width. Once doing so, you should be able to see all the numbers in that field"

    My question to them now is
    "why does the number width gets set to 10. Shouldnt it be based on
    the table description. If it is set to Number(20) then it should be 20. I dont
    want to set the number width for a sql session every time and also I dont want
    to set it in the login.sql file and set it for all the sessions.
    Shouldnt it be dynamic.
    Also how do I find out the current settings of the number width"

    Now I am waiting for their response and will post it when they get back to me.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by marist89
    Are you sure you are displaying all digits after the decimal?
    hmmm, imagine that...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Sep 2000
    Posts
    362
    Originally posted by marist89
    Originally posted by marist89
    Are you sure you are displaying all digits after the decimal?
    hmmm, imagine that...
    Hi Jeff,
    Thanks


    I could not understand what you meant by Are you sure you are displaying all digits after the decimal? ta that time.


    Anyways any Ideas why shouldnt it be dynamic.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    10 is the default width for this type. You can change the defaults either in your login.sql or glogin.sql initialization script for sqlplus.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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



Click Here to Expand Forum to Full Width