Null date in transformer

[Mat01]

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:

Code: Select all

If IsNull(<Date Field>) Then SetNull() Else <Date Field>     

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

[kommven]

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

[ray.wurlod]

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.

[Mat01]

Thank you for your help.

NLS is not installed on our server

Regards,

Mat

[ray.wurlod]

Shouldn't it have been

Code: Select all

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

?

[Mat01]

Ray,

This test

Code: Select all

If IsNull(<Date Field>) Then SetNull() Else <Date Field>

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:

Code: Select all

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

[rasi]

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

[Mat01]

Hi Rasi,

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

Regards,

Mat

[rasi]

Mat

Is it possible to post your full conditions

[Mat01]

Sure,

Here is the derivation for the Stage Variable svFermSpec:

Code: Select all

out_diff_mc.change_code = 2

And the derivation for the field itself (date 10 nullable):

Code: Select all

If IsNull(out_diff_mc.DAT_FERM_MACR) Then 
	(If svFermSpec Then
		e30_dat_fin_mens
	Else SetNull()) 
Else out_diff_mc.DAT_FERM_MACR

The error I get is:
APT_CombinedOperatorController,3: Field 'DAT_FERM_MACR' from input dataset '0' is NULL. Record dropped.

Thanks,

Mat

[rasi]

Mat

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

[Mat01]

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

[kumar_s]

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?

[Mat01]

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

[kumar_s]

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().