Page 1 of 1
Problem while Reading Float DataType from DataBase
Posted: Tue Dec 20, 2011 3:19 pm
by Mandy23
Hi ,
I am trying to read a column BMIPercentile defined as FLOAT in SQL Server Database using ODBC Connector Stage. I have specified column definition as Type=Float , Length=53 , Scale=10.
My Job is aborting with below Fatal error .
Schema reconciliation detected a size mismatch for column BMIPercentile. When reading database column DFLOAT into column SFLOAT, truncation, loss of precision or data corruption can occur
Let me know what should be the correct Column definition to be defined ?
Re: Problem while Reading Float DataType from DataBase
Posted: Tue Dec 20, 2011 4:42 pm
by samdsx
If you will not get really large numbers, cast to decimal data type in query.
Posted: Tue Dec 20, 2011 4:47 pm
by Mandy23
Hi Sam,
My case is I have to read data form table and update the same table again. I do not want to lose data by conversion errors.
Posted: Tue Dec 20, 2011 5:48 pm
by samdsx
It should work fine with decimal, please try and let us know the results
Posted: Wed Sep 04, 2013 7:53 am
by iq_etl
I have the same issue. I'm reading a source table with a column defined as a FLOAT in SQL Server Database using ODBC Connector Stage. Exact same definition as post in the OP.
What is meant by 'cast to decimal type in query'. Is this writing an SQL query and doing a conversion on the column from a FLOAT to a DECIMAL? Would FLOAT be left as the column type in the column tab?
Thanks!
Posted: Wed Sep 04, 2013 7:59 am
by chulett
iq_etl wrote:What is meant by 'cast to decimal type in query'.
CAST function. As to your last question - no, it would be whatever data type you are casting to.
Posted: Wed Sep 04, 2013 2:12 pm
by ray.wurlod
Specify Double as the data type the DataStage table definition. Some databases, including Sybase and SQL Server, report Float for DFloat data types.
Posted: Wed Sep 04, 2013 5:07 pm
by SURA
Once this specific issue is fixed, please do compare the precision values between source and target.
Posted: Thu Sep 05, 2013 2:54 am
by priyadarshikunal
I second SURA on this. I have seen the value in scale getting changed from SQL server and Oracle, but it was fine till 12-13 digits (Can't remember exactly). But, It was from SQL server 2000 to Oracle 10g.
Posted: Thu Sep 05, 2013 8:10 am
by iq_etl
ray.wurlod wrote:Specify Double as the data type the DataStage table definition. Some databases, including Sybase and SQL Server, report Float for DFloat data types.
This worked. I previously understood that Double was reserved for very long numbers. Is using Double a best practice here due to the nature of SQL Server database or should I go with CAST?
Thanks!