Source and target values not getting matched
Moderators: chulett, rschirm, roy
Source and target values not getting matched
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,
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,
-
- Participant
- Posts: 2
- Joined: Thu Nov 10, 2011 11:54 pm
- Location: Mumbai
Re: Source and target values not getting matched
Dear Srini,
Have you tried the conversion function?
Which of the stage you are using?
Regards,
Sanjay
Have you tried the conversion function?
Which of the stage you are using?
Regards,
Sanjay
it's my life
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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,
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,
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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,
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,
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.srini.dw wrote:If I don't use as ASInteger, Iam getting the values as below from 1st job target table.
1.12267e+007
1.54196e+007
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon