Standardization & Validation : Lame question

[vsi]

Hi,

If we have got fields as "Not Null" in the source. And the target requires it to be "Not Null" as well. Do we need to check whether it is nullable or not ??

For the other fields which are "NULL" in the source and "NOT NULL" in target. Should we replace them with some value as "unknown" in standardization or validation?

Thanks

[us1aslam1us]

1. You can check to say the field is NOT NULL . so don't worry about that.

2. This is a good practice to do If there is some neccessary lookup or join (or some business rule)has to be performed downstream on these fields orelse i think there is no need to change the metadata.

Sam

[I_Server_Whale]

Hi,

For the other fields which are "NULL" in the source and "NOT NULL" in target. Should we replace them with some value as "unknown" in standardization or validation?

Thanks

Usually, the incoming NULL values are replaced with a default value. These default values are generally defined by the business.

So, say you getting a date field as NULL, then a default value of '01-01-1900' may be assigned to it.

Whale.

[narasimha]

My two cents

If we have got fields as "Not Null" in the source. And the target requires it to be "Not Null" as well. Do we need to check whether it is nullable or not ??

You don't need to check it, if it was null. If it was then your source would complain

For the other fields which are "NULL" in the source and "NOT NULL" in target. Should we replace them with some value as "unknown" in standardization or validation?

This depends on your business requirement, they might need a "NONE" instead of "unknown" or even want it to be NULL, you never know!

[ShaneMuir]

There could be other nuances as well that would make it prudent to give a default value in your job rather than let the database default it.

1. Be sure to check for empty strings as well as NULL.
I know that our Oracle DB treats an empty string as NULL and even if you have 'not nullable' set in the database with a default value - it will still reject with an error saying can't insert a null value.
2. In the Oracle DB if you have a default value set then if you want it to actually work apparently you shouldn't provide that column name in your insert query. Which can be a pain when you are using an OCI stage with a column generated query. It basically means you have to provide a default value regardless of the set up of the database.

[ray.wurlod]

If the source is a database where NOT NULL constraints are enforced, then you do not need to check.

If the source is, say, a text file (where NOT NULL constraints are meaningless) then you do need to check.

It's not a lame question - it's a good question, and highlights the fact that DataStage server jobs are tolerant of data type (and nullability) mismatches.