Problem while Reading Float DataType from DataBase

Mandy23 · Post by **Mandy23** » Tue Dec 20, 2011 3:19 pm

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 ?

samdsx · Post by **samdsx** » Tue Dec 20, 2011 4:42 pm

If you will not get really large numbers, cast to decimal data type in query.

Mandy23 · Post by **Mandy23** » Tue Dec 20, 2011 4:47 pm

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.

samdsx · Post by **samdsx** » Tue Dec 20, 2011 5:48 pm

It should work fine with decimal, please try and let us know the results

iq_etl · Post by **iq_etl** » Wed Sep 04, 2013 7:53 am

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!

chulett · Post by **chulett** » Wed Sep 04, 2013 7:59 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 04, 2013 2:12 pm

Specify Double as the data type the DataStage table definition. Some databases, including Sybase and SQL Server, report Float for DFloat data types.

SURA · Post by **SURA** » Wed Sep 04, 2013 5:07 pm

Once this specific issue is fixed, please do compare the precision values between source and target.

priyadarshikunal · Post by **priyadarshikunal** » Thu Sep 05, 2013 2:54 am

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.

iq_etl · Post by **iq_etl** » Thu Sep 05, 2013 8:10 am

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!

DSXchange

Problem while Reading Float DataType from DataBase

Problem while Reading Float DataType from DataBase

Re: Problem while Reading Float DataType from DataBase