Hi,
While doing lookup to the reference table i am having below warning message.
Message information:
APT_CombinedOperatorController(2),0: Field 'Col3' from input dataset '0' is NULL. Record dropped.
Data set data : All columns having nulls are allowed
Col1,Col2,Col3,Col4
"1000","abc","ccc","20"
"1000","bbc","KKK","30"
Reference data: All columns having nulls are allowed
Col1,Col2,Col3,Col4
"1000","abc","ccc","20"
"1000","bbc","ddd","30"Code: Select all
ref_table | | | V Dataset-------->lookup stage------------>transformer-----------------> table | | | | | V dataset1 Constraint added inthe transformer: (Constraint added in the transformer NullToEmpty (Col1) ='' Or NullToEmpty (Col2) ='' Or NullToEmpty (Col3) ='' ) Lookup stage -->lookup is based on the four columns (Col1,Col2,Col3,Col4)
As I using condition not met -->continue and also look up failure -->continue. So i am sure i will get null after passing the look up stage.
in the transformer added a constraint (NullToEmpty (Col1) ='' Or NullToEmpty (Col2) ='' Or NullToEmpty (Col3) ='' )
Based on the fourms i have handled nulls but still i am facing the same warning.
Any help greatly appreciated.
Thanks
Suresh
input dataset '0' is NULL. Record dropped
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
input dataset '0' is NULL. Record dropped
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
Check with isnull() and also empty space.
Eg: IsNull(Col1) or Col1='' or Col1='NULL' if you assigned 'NULL' in previous stages
I hope this works.
Thanks'
Vijay
Eg: IsNull(Col1) or Col1='' or Col1='NULL' if you assigned 'NULL' in previous stages
I hope this works.
Thanks'
Vijay
suresh_dsx wrote:Thanks for the swift response but having the same problem tried two options
Option1 )
Directly added Constraint
IsNull(Col1) or IsNull(Col2) or IsNull(Col3)
Option2 )
First added Stage variable1 then used the stage variable in the constraint
STG1--- > IsNull(Col1) or IsNull(Col2) or IsNull(Col3)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Although documented as returning NULL, a failed lookup can return "" instead when. This is particularly true if the output column is declared not nullable (and has a string data type - numeric data types can be set to 0).
Tip: make use of the fact that the Len() function returns 0 when its argument is null to test for null and "" with a single function call.
![Idea :idea:](./images/smilies/icon_idea.gif)
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.