DSXchange

Hi all,

I have an issue with a date field that may contain null values. The logic to apply is:
if condition X and the date field is null then put a defined date (job parameter)
Else if Not condition X and the date field is null then put null
Else put the date field

This logic always result in the transformer dropping the records because the field contains a null.

If I simply put the following test: It works perfectly

So I did the following, I put the test above in a stage variable and in my derivation, I test:
If Condition X Then put defined date Else put Stage variable

This also ends up in the record being dropped.

How could I apply the correct logic while respecting DS's sensitivity about nulls?

Thanks,

Mat

Is NLS enabled on this Job?
If "YES" IsNull will not work for UStrings.
Make sure that Date field is not Ustring.

kommven wrote:Is NLS enabled on this Job?
If "YES" IsNull will not work for UStrings.
Make sure that Date field is not Ustring.

Is that fact (that IsNull() does not work for ustring when NLS is enabled) documented anywhere? As far as I was aware, IsNull() should work with any data type, whether or not NLS is enabled.

Thank you for your help.

NLS is not installed on our server

Regards,

Mat

Shouldn't it have been ?

Ray,

This test
was only done to verify the functionality of the IsNull() function on my nullable date field.

The real derivation That I would like to implement is:

Mat

If IsNull(<Date Field>) Then
If Condition X Then
#DefaultDate#
Else SetNull()
Else <Date Field>

In your statement the first IsNull will not have any problem. But in your "Condition X" if you still use the <Date Field> without having null handling then the records will be dropped. Make sure that you inside condition also has proper null handling

(ie)

If IsNull(<Date Field>) Then
If Len(NullToValue((<Date Field>) ,'')) > 0
Then 'Success'
Else 'Failed'
Else 'Null Datel'


Thanks

Hi Rasi,

Condition X using other fields that are not nullable (specifically, a change_code from a change capture stage).

Regards,

Mat

Mat

Is it possible to post your full conditions

Sure,

Here is the derivation for the Stage Variable svFermSpec: And the derivation for the field itself (date 10 nullable): The error I get is:
APT_CombinedOperatorController,3: Field 'DAT_FERM_MACR' from input dataset '0' is NULL. Record dropped.

Thanks,

Mat

Mat

Are you using this out_diff_mc.DAT_FERM_MACR field in any other Stage Variables?...

No, I have only this one stage variable and no other derivation uses this field.

Make sure that Date field is not Ustring.

How can be a Date be Usting

out_diff_mc.change_code = 2

Are you checking any value or assiginging a value of 2 to out_diff_mc.change_code in the stage variable svFermSpec?

What is the value of e30_dat_fin_mens. Is your output field nullable?

Hi Kumar,

Are you checking any value or assiginging a value of 2 to out_diff_mc.change_code in the stage variable svFermSpec?

No, this is only the lazy way of writing: If out_diff_mc.change_code = 2 Then @true Else @false

What is the value of e30_dat_fin_mens. Is your output field nullable?

e30_dat_fin_mens is date parameter = '2005-10-31'.
The output date field is nullable.

I made a test changing the source and destination field from a date to an integer and it worked fine. I think there might be a bug with date null handling...

Regards,

Mat

If your date column is nullable, it should not get dropped.
Atleast it should hold '*********' in it.
Is you date parameter e30_dat_fin_mens converted to date format. i.e., if it is assigned as string, try with StringToDate().