Dropping records
Moderators: chulett, rschirm, roy
Dropping records
What could be the reason for the Transformer to drop records with NOT NULL values ?
Well... more than likely, because you told it to. 
Seriously, Transformers don't "drop" records on their own. Records either don't pass through because of a constaint or look like they didn't because they've been rejected by the target stage. I would guess that the later is your case.
Need. More. Information. What's on the other end of your Transformer? Do you have a Reject link? Are there any warnings in the log for the job?
Seriously, Transformers don't "drop" records on their own. Records either don't pass through because of a constaint or look like they didn't because they've been rejected by the target stage. I would guess that the later is your case.
Need. More. Information. What's on the other end of your Transformer? Do you have a Reject link? Are there any warnings in the log for the job?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
NULL values can cause strange results in some functions such as concatenate. If you concatenate any value with a null value you get null back again, which can cause constraints to fail. If you know a specific row that is dropping out try running in debug mode and stepping through the job one link at a time.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Anywhere where there's a two-way test, one path is taken if the expression is true, the other path is taken if the expression is not true.
Note that I deliberately said "not true" rather than "false".
Where a value is NULL, we can not safely assert that it is true, so it must take the other path. This is the DataStage rule for handling NULL in a Boolean context.
Constraint expressions must be true in order for the row to be processed onto that link. Therefore, if the result of the expression is NULL, or zero, or "", then the expression is not satisfied.
Note that I deliberately said "not true" rather than "false".
Where a value is NULL, we can not safely assert that it is true, so it must take the other path. This is the DataStage rule for handling NULL in a Boolean context.
Constraint expressions must be true in order for the row to be processed onto that link. Therefore, if the result of the expression is NULL, or zero, or "", then the expression is not satisfied.
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.
We had the same issue in 6.0 with PX and this issue was resolved in 7.0 partially. There are 2 scenarios as follows.
1. Derive COL2 based on COL1.
Example: COL1 = 1000
The derivation of the COL2 is if COL1 = 1000 then "A" Else "B". PX transformer would not reject the record if the COL1 has null value.
2. Derive COL1 based on COL1
Example: COL1 = 10000
The derivation of the COL1 is COL1/10. PX transformer would reject the record if the COL1 has null value. Server job would not reject the record even though the COL1 has null value and it will assign NULL to COL1 and process the next row.
Hope this would clarify.
Regards
Saravanan
1. Derive COL2 based on COL1.
Example: COL1 = 1000
The derivation of the COL2 is if COL1 = 1000 then "A" Else "B". PX transformer would not reject the record if the COL1 has null value.
2. Derive COL1 based on COL1
Example: COL1 = 10000
The derivation of the COL1 is COL1/10. PX transformer would reject the record if the COL1 has null value. Server job would not reject the record even though the COL1 has null value and it will assign NULL to COL1 and process the next row.
Hope this would clarify.
Regards
Saravanan