Purpose of setting Nullable property as 'No'??
Moderators: chulett, rschirm, roy
Purpose of setting Nullable property as 'No'??
I am quite confused as to what is the real purpose of setting the Nullable property as 'No'.
In a job, I had to read a field with a BigInt datatype from a sequential file. It was a key field, so I set the Nullable to 'No'. While testing it (reading the sequential file input), I gave a Null value for the field, and as expected PX rejected that record as it had a null value in a non-nullable field.
Now I am developing another similar job, except that the field has Date datatype. I set Nullable to 'No' like earlier and gave a null value as input for testing it. Now PX reads the record properly and does not reject the record!!! I tried with Varchar and again the record is not rejected.
The sequential file input is read properly even when there are null values in the input, and when the fields are specified as Nullable 'No' with datatype like Varchar and Data.
If it is going to allow null values in the input, what exactly is the purpose of setting the Nullable property?
Thanks in advance!
In a job, I had to read a field with a BigInt datatype from a sequential file. It was a key field, so I set the Nullable to 'No'. While testing it (reading the sequential file input), I gave a Null value for the field, and as expected PX rejected that record as it had a null value in a non-nullable field.
Now I am developing another similar job, except that the field has Date datatype. I set Nullable to 'No' like earlier and gave a null value as input for testing it. Now PX reads the record properly and does not reject the record!!! I tried with Varchar and again the record is not rejected.
The sequential file input is read properly even when there are null values in the input, and when the fields are specified as Nullable 'No' with datatype like Varchar and Data.
If it is going to allow null values in the input, what exactly is the purpose of setting the Nullable property?
Thanks in advance!
Joel Satire
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Go to the extended properties of the field you want to set as Not nullable, and set the Null Value to the value which you want to treat as a NULL.
For a varchar you would set the
This property tells datastage to treat the value which you set as a NULL and then your job should work fine.
For a varchar you would set the
Code: Select all
NullValue = ''
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Go to the extended properties of the field you want to set as Not nullable, and set the Null Value to the value which you want to treat as a NULL.
For a varchar you would set the
This property tells datastage to treat the value which you set as a NULL and then your job should work fine.
For a varchar you would set the
Code: Select all
NullValue = ''
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
Sorry if I have been confusing, let me clarify...
I want PX to tell me when a null value is coming in the input field 'Field1'. But Field1 is a key column and hence cannot be nullable. In the event of a null coming in, the record should be rejected...
I thought this was the purpose of setting Nullable 'No'...
Andrw, I couldn't understand what exactly you meant when u said that you used default in both places,
Minjuddin, do you mean the Null Field Value property?
I want PX to tell me when a null value is coming in the input field 'Field1'. But Field1 is a key column and hence cannot be nullable. In the event of a null coming in, the record should be rejected...
I thought this was the purpose of setting Nullable 'No'...
Andrw, I couldn't understand what exactly you meant when u said that you used default in both places,
Minjuddin, do you mean the Null Field Value property?
Joel Satire
Ok, let us take things step by step. Normally this type of problem starts with mistaking an ASCII NUL and SQL NULL values for the same thing. Normally, a sequential file doesn't contain SQL NULLs. So as the first step let us see what kind of values we are dealing with. Use your favorite editor to look at your file and determine if you are seeing CHAR(000) {likely} or CHAR(128) {which is SQL Null, and unlikely}. Also, tell us if just one of these characters occurs or if the whole string or number is filled with repetitions of the character.