Null values getting dropped by SCD Stage

[pierre.nelson@hotmail.com]

Hi,

I am using the SCD stage to update a dimension. I have a date field (active_start_date) which I have defined as a type 2 field. Most of the time, this field is null. My columns going into and out of the SCD stage allow nulls. I beleive what is happening is that DS is trying to compare a NULL value with a NULL value to determine if something has changed and it doesn't like it.

I cannot very well flip NULL values to a "special code" as the SCD output link needs to go directly to a database stage so that the metadata (whether to update a field, or insert a new record, etc ...) is interpreted correctly.

Any help?

[vmcburney]

Does this field ever change? Seems like the type of field that gets locked in once the source record is created and is not in fact a slowly changing field. You might be able to switch the field over to the type that doesn't get compared.

[pierre.nelson@hotmail.com]

Yes the field does change. I also have a EDW_CREATION_DATE and EDW_DISABLE_DATE that behave like the field you are referencing ... and I'm using the Effective Date (Type 2) and Expiration Date (Type 2) purpose for these fields.

[pierre.nelson@hotmail.com]

Just for kicks, I modified my SQL (for the source) so that there was a non-null value for each record coming accross.

First run - everything is fine

Second run - if some of the date values have been modified, I get the following on the output to the SCD ... the one that updates the dimension:

Updated_edwPLANNING_OBJECT,1: Failure during execution of operator logic.
Updated_edwPLANNING_OBJECT,1: Fatal Error: Invalid Julian day

No need to ask if the date is invalid - it's fine and it's coming from an Oracle DB.

[pierre.nelson@hotmail.com]

This is a known bug to IBM. The fix for it will be available in 8.0.1.1 in the mid to end of Sept. 2007. For now - if you have any nulls in either your source or dimension which need to be type1 or type2 ... it won't work.