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
Standardization & Validation : Lame question
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Re: Standardization & Validation : Lame question
Usually, the incoming NULL values are replaced with a default value. These default values are generally defined by the business.vsi wrote: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
So, say you getting a date field as NULL, then a default value of '01-01-1900' may be assigned to it.
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Re: Standardization & Validation : Lame question
My two cents
You don't need to check it, if it was null. If it was then your source would complainvsi wrote: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 ??
This depends on your business requirement, they might need a "NONE" instead of "unknown" or even want it to be NULL, you never know!vsi wrote: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?
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.