6 Replies Latest reply: Oct 18, 2012 8:45 AM by 969058 RSS

    Float values being rounded when fetching from Teradata

    969058
      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
          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
            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
              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
                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
                  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
                    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.