Dropping null record in the transformer before loading oracl

[dstest]

Hi,

I am getting following warning in the transformer stage before loading into oracle table.

xfm_tranformer,0:Field 'CLAIM_KEY' from input dataset '0' is NULL. Record dropped.

I am using the following condition in the transformer.

If NullToEmpty(inS1xfm.CLAIM_KEY)="" Then "" Else inS1xfm.CLAIM_KEY

DataType : Bigint(Input and output)

This is happening in production env and i ran the same file against the Test env and it ran successfully with out dropping any records.Can any one please tell me whether it is env issue or any issue with my logic.

Thanks

[ray.wurlod]

What's different (a) between the two environments and (b) between the data in the two environments? Have you checked that it is exactly the same job in each environment?

[dstest]

a)There is no difference between the two enviroments.we checked with Datastage admin regarding this.

b)After got this error we took the same file and processed in the test env and there is no difference in the data becusae it is the same file.
we checked all the oracle table metadata between two environments and both are same,and both the jobs are exactly same and there is no difference.Even we imported the same job from prod to test and ran that job but in test it ran successfully with out dropping record.

Is there any we can find if there is any difference between two datastage environments.


Thanks

[ray.wurlod]

a)There is no difference between the two enviroments.we checked with Datastage admin regarding this.

Is there any we can find if there is any difference between two datastage environments.

Check with the Datastage admin?


Seriously, though, your diagnosis has been systematic thus far. But you're only checking DataStage. Are there, say, database differences? Differences in null handling in the importing stage?

[dstest]

importing stage means oracle table.I have already checked table metadata between prod and test databases and both are exactly same.
Can you please tell me any other things i need to check.

Thanks

[ray.wurlod]

I think it's probably worth re-thinking your logic. NullToEmpty(inS1xfm.CLAIM_KEY) implies a "cast" from BigInt to String and then your Else clause converts it back to BigInt - however your Then clause generates a value ("") that can not be successfully converted back to BigInt. Why not just use

Code: Select all

If IsNull(inS1xfm.CLAIM_KEY) Then SetNull() Else inS1xfm.CLAIM_KEY

There may be one or more of the null-handling specifications different between the two environments - you would need to check these individually (for example environment variable settings). I'm not at a site using parallel jobs at the moment, so can't advise actual environment variable names, but chances are the names will contain "NULL".