DSXchange

Posted: **Thu Jan 20, 2011 12:00 am**

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

Posted: **Thu Jan 20, 2011 12:24 am**

could you check with the "IsNull" Or "IsNotNull) function.. instead of the NullToEmpty or NullToValue...

let us know the output

Posted: **Thu Jan 20, 2011 12:40 am**

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)

Posted: **Thu Jan 20, 2011 1:52 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

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)

Posted: **Thu Jan 20, 2011 4:29 am**

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.