This discussion is archived
6 Replies Latest reply: Oct 18, 2012 6:45 AM by 969058 RSS

Float values being rounded when fetching from Teradata

969058 Newbie
Currently Being Moderated
I am trying to query a Teradata database by using a link from OracleXE. When I fetch a value defined in Teradata as a 'float', the value is automatically being rounded.

Value when querying Teradata from OracleXE:
0.0166979

Value when directly querying Teradata:
0.01669787586530

Has anyone encountered this before. I have tried to cast the value to a number/varchar2 within Oracle, but it seems as if the number is being rounded prior to being able to apply any data type conversions.
  • 1. Re: Float values being rounded when fetching from Teradata
    damorgan Oracle ACE Director
    Currently Being Moderated
    You wrote: "using a link." Could you be more specific and include version numbers?

    Please also provide the query DML.
  • 2. Re: Float values being rounded when fetching from Teradata
    969058 Newbie
    Currently Being Moderated
    OracleXE version= 11.2.0.2.0
    Teradata version = 13.00.00.26

    select t.float_column from user.table@local_to_edw t

    local_to_edw is the name of the link. I have setup a tns entry to utilize the System DSN data source I have setup to connect to Teradata.
  • 3. Re: Float values being rounded when fetching from Teradata
    damorgan Oracle ACE Director
    Currently Being Moderated
    My instinct now is to suggest you open an SR with Oracle as it is obviously a data type mismatch ... but then you are using XE so that isn't likely to help.

    Can you replicate using SE or EE and open an SR?

    Also consider trying using PL/SQL where you define the Oracle data type as a FLOAT data type. For example:
    DECLARE
     x FLOAT(63);
    BEGIN
      SELECT ...
      INTO x
    or
    DECLARE
     x BINARY_FLOAT;
    BEGIN
      SELECT ...
      INTO x
    Although it just occurred to me Oracle may be bringing in the full number but not showing it to you based on default formatting. Try this in SQL*Plus before running your query:
    col <teradata_column_name> format 9999.999999999999999
    and you should be able to try this as well to see what is really coming over:
    SELECT dump(<teradata_column_name>)
    FROM ...
  • 4. Re: Float values being rounded when fetching from Teradata
    969058 Newbie
    Currently Being Moderated
    Can you replicate using SE or EE and open an SR?
    --No, I am not privy to make that connection to teradata in that environment here, that is why I have local version of XE.*
    Also consider trying using PL/SQL where you define the Oracle data type as a FLOAT data type. For example:
    --1st option, same rounded value returned.
    --2nd option, received ORA-28528: Heterogeneous Services datatype conversion error
    DECLARE
    x FLOAT(63);
    BEGIN
    SELECT ...
    INTO x
    or
    DECLARE
    x BINARY_FLOAT;
    BEGIN
    SELECT ...
    INTO x


    Although it just occurred to me Oracle may be bringing in the full number but not showing it to you based on default formatting. Try this in SQL*Plus before running your query:
    col <teradata_column_name> format 9999.999999999999999
    --Tried this, but recieved just padded 0's at the end of the rounded value.*

    and you should be able to try this as well to see what is really coming over:
    SELECT dump(<teradata_column_name>)
    FROM ...
    --Received the following:*
    Typ=2 Len=5: 192,2,67,98,91
    So it looks like it recognizes it as Float/Number
  • 5. Re: Float values being rounded when fetching from Teradata
    damorgan Oracle ACE Director
    Currently Being Moderated
    Looks like the issue may be with the driver on the TD side. You are going to need to dig deeper on both sides of the connection.

    But one more try:
      1  create table test (
      2  num_col number,
      3  dp_col  double precision,
      4  real_col real,
      5* binfl_col binary_float)
    t3a1> /
    
    Table created.
    
    t3a1> desc test
     Name                                                                          Null?    Type
     ----------------------------------------------------------------------------- -------- ------------
     NUM_COL                                                                                NUMBER
     DP_COL                                                                                 FLOAT(126)
     REAL_COL                                                                               FLOAT(63)
     BINFL_COL                                                                              BINARY_FLOAT
    
    t3a1> insert into test values (1.234567890,1.234567890,1.234567890,1.234567890);
    
    1 row created.
    
    t3a1> select dump(num_col), dump(dp_col), dump(real_col), dump(binfl_col)
      2  from test;
    
    DUMP(NUM_COL)
    ----------------------------------------------------------------------------------------------------
    DUMP(DP_COL)
    ----------------------------------------------------------------------------------------------------
    DUMP(REAL_COL)
    ----------------------------------------------------------------------------------------------------
    DUMP(BINFL_COL)
    ----------------------------------------------------------------------------------------------------
    Typ=2 Len=6: 193,2,24,46,68,90
    Typ=2 Len=6: 193,2,24,46,68,90
    Typ=2 Len=6: 193,2,24,46,68,90
    Typ=100 Len=4: 191,158,6,82
    See how it reacts to a variable/column of data type 100 (BINARY_FLOAT)
  • 6. Re: Float values being rounded when fetching from Teradata
    969058 Newbie
    Currently Being Moderated
    Thanks for continually providing suggestions.

    As far as the driver is concerned, I was convinced that was the problem as well. Looks like the driver I have is v 13.00.00.06, which is dated 01/2010. There is a new one from this year. But I have also installed SQL Server express and used the exact same driver, and I don't have a rounding issue when connecting to Teradata like I do in OracleXE. So my assumption is the driver is not the culprit.

    I have tried what you suggested as far as the temp table and inserting into a binary_float, same value was inserted.

    create table umm11.test_float_issue (
    num_col number,
    dp_col double precision,
    real_col real,
    binfl_col binary_float);

    insert into umm11.test_float_issue values (
    (select t.float_valuefrom user.table@local_to_edw t),
    (select t.float_valuefrom user.table@local_to_edw t),
    (select t.float_valuefrom user.table@local_to_edw t),
    (select t.float_valuefrom user.table@local_to_edw t)
    ) ;

    select num_col, dp_col, real_col, binfl_col
    from umm11.test_float_issue;

    I received the following for each value 0.0166979, even though the value in the source = 0.01669787586530.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points