DFLOAT into column SFLOAT issue

[srini.dw]

Hi,

Please need your help to resolve fatal error issue.

The job design is ODBC Connector -> Copy -> DataSet

When I run the job, Iam getting the fatal error as below

OC_Connector: Schema reconciliation detected a size mismatch for column ConversionFactor. When reading database column DFLOAT into column SFLOAT, truncation, loss of precision or data corruption can occur. (CC_DBSchemaRules::reportSizeMismatch, file CC_DBSchemaRules.cpp, line 1,653)

One of the source column ConversionFactor is Float in source and having below values.

0.00083110025
0.001119538
3.707377834375
9999999
18585.5794375
40.3399

Right now the column ConversionFactor is having Float in the Dataset.

Any help would be appreciated.

Thanks,

[priyadarshikunal]

Depending on the source database type it can be treated as dfloat/sfloat. You can define the column as double and then do the conversion using transformer.

Or since you are using connector, I think there should be a property where you can set what to be done in case of schema mismatch. I that case the job will not abort but please verify if there is no dataloss.

[srini.dw]

Thanks for the reply.

Source is SQL server.

You mean define the column as double in OC connector and do the conversation i.,e double to float in transformer.

Any idea how to convert.

There's a option Schema reconciliation -> Fail on size mismatch -> NO.

It worked, but there's a data loss.

Thanks,

[ray.wurlod]

Keep dfloat (Double) throughout.

Any time you try to shoehorn a dfloat into an sfloat you will get data loss.

[srini.dw]

Thanks for the reply.

I have tried to keep column SQL type as double in all 3 stages, but the column ouput is getting data loss as show below.

I/P -> O/P
0.00083110025 -> 0.0008311
0.001119538 -> 0.00111954
18585.5794375 -> 18585.6

Thanks,

[priyadarshikunal]

View data can be deceiving. The examples you gave hardly has 8 scale which dfloat (double) should not have any trouble handling. Make sure you have double mentioned throughout, including stage variables if you are using one to hold this field.

for a test dump the output to peek stage after converting it to string or decimal(38,16) and see if you actually have data loss by keeping it dfloat as well.

[srini.dw]

Thanks,

Cannot keep Float as datatype in OC connector, its getting failed.

Simple design.

1. OC connector -> Transformer -> Peak Stage
Result - >Getting failed, source datatype is Float

2. OC connector -> Transformer -> Peak Stage
Result - >Getting Success. source datatype is Double

I did conversation of DfloatToDecimal(Input) and iam not getting data as expected

Source -> Output
130.0625470625 -> 0000000000000000000130.0625470625000192
655.9845365 -> 0000000000000000000655.9845364999999488

Right now the output datatype is decimal (38,16)

Any other function other than DFloatToDecimal should be used.

Thanks,

[ray.wurlod]

It is a given that computers cannot accurately store floating point numbers above a certain size. The IEEE published standards that are used widely and which offer the best compromise.

[srini.dw]

Thanks,

Does it mean the values below coming from source (SQL server) as Float cannot be stored in the Datasets.

0.00083110025
0.001119538
3.707377834375
18585.5794375
40.3399
130.0625470625
655.9845365


I did not get "The IEEE published standards that are used widely and which offer the best compromise."

Thanks,

[ray.wurlod]

When you convert to Decimal specify as much scale as you need. But there is a limit to the accuracy to which floating point numbers can be stored.

[srini.dw]

Thanks for the reply.

Sorted out the issue, no need of any conversation, when we load to Oracle, the values are getting matched.

Thanks,