DSXchange

We're using an SCD stage with a sequential source (comma delimited with double-quote enclosures) and Oracle Lookup/Target.



The initial run completed successfully, however the "change" test failed:
The field that is failing is not used in any functions, constraints, etc. It is mapped straight through the job without transformation. This field is nullable throughout the job.

However, this field is purposed as 'Type 2' and is compared to the value in the lookup/target.

The field for the matching record is NULL in the lookup/database as well.

The only thing I can think of is that the SCD stage is having issues with NULL during the Type 2 compare.

Any thoughts on how to correct this? I've tried NullToEmpty() in the Transformer Stage before the SCD as well as within the Upsert Derivation.

You mean to say, you getting this error even after this Null handling??
Make the Combinability Mode to False. So that you can nail down the error.
Does that DVP used in any of derivation Expression other than straight mapping?

kumar_s wrote:Does that DVP used in any of derivation Expression other than straight mapping?

There are absolutely no derivation expressions on this field.

If I convert the incoming NULL value to empty sting or something else, then it wouldn't match the value being returned from the lookup...and would force a Type 2 change.

Is there a standard setting for NULL comparison in SCDs/Lookups?

Thanks in advance.

kumar_s wrote:Make the Combinability Mode to False. So that you can nail down the error.

Good Idea. This points out that it is the SCD stage:

scdT_EMP_PROFILE,0: Field 'DVP' from input dataset '0' is NULL. Record dropped.

Any thoughts on how to handle NULLS in an SCD?

May it could be simple databae NULL to NULL compare, which will obviously fail. Try to do the Same Null handling before SCD stage. On both input and reference link.

kumar_s wrote:May it could be simple databae NULL to NULL compare, which will obviously fail. Try to do the Same Null handling before SCD stage. On both input and reference link.

Thanks for the response.

So there is no job-level setting that will more cleanly handle NULLs within Parallel jobs? I would think that comparing NULLs is a common occurrence in SCD processing as not every field is populated 100% of the time.

I was hoping that it isn't necessary to implement NULL handling logic on every single input and reference field.

I'm not sure it is possible to handle NULLs in the reference link. According to everything I've read, the Oracle Enterprise Stage (reference) is to be directly connected to the SCD stage (so that it can dynamically create the SQL). Can I put a Transform Stage in between the two?

You can have NVL function in Oracle select query to convert varchars/chars to spaces and Number to 0 or all 9's/

As I suspected (read: hoped), this is a known bug and is resolved in Fix Pack 1a. We will install the pack tomorrow and retest.

Bottom line, the SCD stage should be able to handle NULLs in the comparison without kludging a special NULL transformation for each input and reference field. Let's hope Fix Pack 1a gets it right.