Source and target values not getting matched

[srini.dw]

Hi Guys,

Please need your help, source column and target columns values are getting mismatched.

In the 1st job, source column i.e Value_Id(datatype Integer) is getting mapped to Varchar.

In the 2nd job, for the same column Iam using the below logic to format it again.

if IsNull(Value_Id) then SetNull() else AsInteger(NullToEmpty(Value_Id))

In the source (SQL server) values are stored as

144925
122356
11226701
15419609

But in the target(Oracle) Iam getting below values

144925
122356
11226700
15419600

Cannot keep the column as Integer in the target table, because of other issues.

Iam not able to match the values between source and target for few columns.

I have tried with queries like select CAST(Value_Id AS INTEGER) from Table_name, not getting exact ouput as source.

Any ideas how to match exactly in SQL or ETL job.

Thanks,

[avicorp_1983]

Dear Srini,

Have you tried the conversion function?

Which of the stage you are using?

Regards,

Sanjay

[chulett]

What is the data type of the target column in Oracle? In the job?

[srini.dw]

Thanks for the replies.

1st Job (pivot job)
ODBC Connector -> Transformation -> DataSet

2nd Job
Dataset -> Transformer -> CDC -> Target (Oracle)

@Sanjay Have you tried the conversion function, any specific function I need to try.

Value_Id datatype is Varchar in Oracle.

Thanks,

[chulett]

If the first job that populates the dataset converts the integer to a varchar and the Oracle target in the second job in a varchar, why does the second job need to do any 'reformatting'? Try using DecimalToString() in the first job rather than any cast / NullToEmpty / AsInteger shenanigans in the first job.

[srini.dw]

Thanks for reply.

My mistake. The job design is as follows

1st Job (pivot job)
ODBC Connector -> Transformation -> Oracle Connector

2nd Job
Oracle Connector -> Transformer -> CDC -> Target (Oracle)

In the first job, we have used dynamic SQL to insert into the database, hence cannot use DecimalToString().

In the 2nd job, we are using the below function.

if IsNull(Value_Id) then SetNull() else AsInteger(NullToEmpty(Value_Id))

Any thoughts.

Thanks,

[chulett]

Pretty much the same thoughts. If your target is a varchar, why AsInteger? Just move it through.

And not really sure what you mean by 'dynamic SQL' in this particular case and how that would stop you from doing the conversion in the transformer... but ok.

[srini.dw]

Thanks for reply.

If I don't use as ASInteger, Iam getting the values as below from 1st job target table.

1.12267e+007
1.54196e+007

I need to convert this.

Dynamic PLSQL, is which parameters are explicitly identified.

Thanks,

[chulett]

This PL/SQL, is that what you are using in the target connector in the second job? Or somewhere else?

Not being able to see what you are doing makes this hard to trouble-shoot but you should be fine if you do explicit conversions and only when needed, right now it seems like you are doing both too much (the AsInteger shouldn't be needed) and too little if the first job just has it 'mapped to a varchar' with only an implicit conversion.

[srini.dw]

Thanks for reply.

Dyanmic PLSQL is used in the 1st job, Its a implicit conversion here.

In the 2nd job, trying to do explicit conversion to get the orignal value, with the below code.

if IsNull(Value_Id) then SetNull() else AsInteger(NullToEmpty(Value_Id))

Tried with AsDouble and Asfloat, here the values are coming as exponential.

Thanks,

[BI-RMA]

If I don't use as ASInteger, Iam getting the values as below from 1st job target table.

1.12267e+007
1.54196e+007

Here is why the the values are different in source and target. The precision of the floating-point values is too small to give you the correct values (hence 0 at the final position in decimal format). Try to avoid floating-point variables throughout this job.