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
Dropping null record in the transformer before loading oracl
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
dstest wrote:a)There is no difference between the two enviroments.we checked with Datastage admin regarding this.
Check with the Datastage admin?dstest also wrote:Is there any we can find if there is any difference between two datastage environments.
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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".
Code: Select all
If IsNull(inS1xfm.CLAIM_KEY) Then SetNull() Else inS1xfm.CLAIM_KEY
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.