Null check in Constraints and Derivation

[us1aslam1us]

Could you post the complete constraint? It will be easy to debug if you can specifically tell which records are getting dropped.
Also is it a typo that your IsNotNull doesn't carry the link information.

Code: Select all

IsNotNull(DSLink.ColumnA)
And  
If (Cond1 and Cond2) then Sol1 else Sol2

[snt_ds]

Hi

Code in used in the constraint
IsNotNull(Read_Lnk.P_MKT) And Read_Lnk.P_MKT <> 0

Warning
Detail_Tfm,0: Field 'P_MKT' from input dataset '0' is NULL. Record dropped.



Code in the derivation
(If IsNotNull(Read_Lnk.P_MKT) And Read_Lnk.P_MKT <> 0
Then ''
Else Field(DecimalToString(Read_Lnk.P_MKT , 'fix_zero,suppress_zero') , '.' , 1)
: '.' : Left(Field(DecimalToString(Read_Lnk.P_MKT,'fix_zero') , '.' , 2) : Str('0', 6) , 6) )

Warning
Detail_Tfm,0: Field 'P_MKT' from input dataset '0' is NULL. Record dropped.

Tested 1 condition at a time, both the times i got the same warning -- records dropped

Please suggest

[ArndW]

...IsNotNull(Read_Lnk.P_MKT) And Read_Lnk.P_MKT <> 0
...

An "AND" condition will always evaluate both sides, so this will drop records with null values of Read_Lnk.P_MKT. You need to change your command to either an OR statement checking for nulls first or add a handle_null to convert the null to a legal numeric for your comparison.

[ArndW]

Sure you can use OR - "NULL() or value =0" is the inversion of the condition. Yes, you can do the null to zero - that will solve your null dropped record issue.

[Minhajuddin]

Sure you can use OR - "NULL() or value =0" is the inversion of the condition.

This will throw an error if the column is NULL.. since DS will be evaluating "value = 0" even if it is Null().

The easiest as already mentioned would be

Code: Select all

NullToValue(input.column) <> 0 

or

Code: Select all

IsNotNull(input.column) and NullToEmpty(input.column) <> 0

or

Code: Select all

Not(IsNull(input.column) or NullToEmpty(input.column))

[snt_ds]

Thanks All for you suggestions

finnally iam using

NullToZero(Link.Coulum) <> 0

Thanks Again