Null record getting rejected
Moderators: chulett, rschirm, roy
Null record getting rejected
HI,
iam getting a unpredictable reaction of ds.
In source i have integer field. it contain some NULL value. when i VIEW DATA it doesnt show me those record which contain NULL value.
Nullable in column tab is also chaged to YES.
When i change that particular colomn to Varchar, then it works. i can able to see the row containing Null values as well.
All other setting are as default.
Any idea y it behavies like this.
regards
kumar
iam getting a unpredictable reaction of ds.
In source i have integer field. it contain some NULL value. when i VIEW DATA it doesnt show me those record which contain NULL value.
Nullable in column tab is also chaged to YES.
When i change that particular colomn to Varchar, then it works. i can able to see the row containing Null values as well.
All other setting are as default.
Any idea y it behavies like this.
regards
kumar
Kumar,
integer fields cannot contain null values. Think about it for a minute, integers are represented in a binary form (often excess-128) and use every single possible combination of bits to represent positive and negative numbers.
How would an SQL NULL fit in? There is no way to distinguish between a null value and it's numeric representation? For example, if null is 0x00 (Hex 00) then it would have the same value as in integer 0.
integer fields cannot contain null values. Think about it for a minute, integers are represented in a binary form (often excess-128) and use every single possible combination of bits to represent positive and negative numbers.
How would an SQL NULL fit in? There is no way to distinguish between a null value and it's numeric representation? For example, if null is 0x00 (Hex 00) then it would have the same value as in integer 0.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
Arnd,
I had one doubt about this.
While developing jobs, I have observed that DS converts the integer null to 0s. But if DBs like DB2 are allowing NULLs in integer fileds, why it is not allowed to have nulls in integer fields?
What if I want to insert a null in integer through DS?
I had one doubt about this.
While developing jobs, I have observed that DS converts the integer null to 0s. But if DBs like DB2 are allowing NULLs in integer fileds, why it is not allowed to have nulls in integer fields?
What if I want to insert a null in integer through DS?
Regards,
S. Kirtikumar.
S. Kirtikumar.
Kirtikumar,
I just looked and stand corrected! In our Oracle we can insert a null into an integer field and subsequently SELECT it. Since a binary representation of NULL in a normal integer field isn't possible Oracle must use a flag outside of the field's value to mark it as NULL.
If a field is a string representation of an integer (as would be the case when doing a bulk load) then there is no problem representing NULL - each position has an ASCII value of 0-9 so the value of NULL would fall outside of that range. But when dealing with actual binary numbers that is not the case - since usually the numbers are read in as characters and subsequently converted into an internal representation you also can track a field having a NULL value.
Back to your original question - does your job work correctly (I assume it does), the view-data has always had a number of quirks particularly when it comes to displaying things outside of the normal ASCII range (e.g. extended characters, multibyte). I think you might have discovered a display issue and not a handling problem.
I was looking at your question from a straight programming level, with one integer field. In reality we are working with more storage locations to hold a field's value.
I just looked and stand corrected! In our Oracle we can insert a null into an integer field and subsequently SELECT it. Since a binary representation of NULL in a normal integer field isn't possible Oracle must use a flag outside of the field's value to mark it as NULL.
If a field is a string representation of an integer (as would be the case when doing a bulk load) then there is no problem representing NULL - each position has an ASCII value of 0-9 so the value of NULL would fall outside of that range. But when dealing with actual binary numbers that is not the case - since usually the numbers are read in as characters and subsequently converted into an internal representation you also can track a field having a NULL value.
Back to your original question - does your job work correctly (I assume it does), the view-data has always had a number of quirks particularly when it comes to displaying things outside of the normal ASCII range (e.g. extended characters, multibyte). I think you might have discovered a display issue and not a handling problem.
I was looking at your question from a straight programming level, with one integer field. In reality we are working with more storage locations to hold a field's value.
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
Kumar,
It IS possible to view a field containing a Null with view data in the Sequenial file stage.
if you SFStage is a source you must specify three things.
1) Nullable = Yes :allow the field to be nullable
2) IntegerType (Default) = 'null' :this makes any input error a null
3) Nullable (Null Field Value) = \000 :set the value of a field that contains a null
the data will appear as:
a 100
b 200
c NULL
d 400
It IS possible to view a field containing a Null with view data in the Sequenial file stage.
if you SFStage is a source you must specify three things.
1) Nullable = Yes :allow the field to be nullable
2) IntegerType (Default) = 'null' :this makes any input error a null
3) Nullable (Null Field Value) = \000 :set the value of a field that contains a null
the data will appear as:
a 100
b 200
c NULL
d 400
Lance Short
"infinite diversity in infinite combinations"
***
"The absence of evidence is not evidence of absence."
"infinite diversity in infinite combinations"
***
"The absence of evidence is not evidence of absence."
Hi Lance,
This is great!!!!
yes i could veiw the null value.
but may i know what does the follwoing statement ment for
i thought null is already assigned in the previous statement
regards
kumar
This is great!!!!
yes i could veiw the null value.
but may i know what does the follwoing statement ment for
coz even without this i can view data.Nullable (Null Field Value) = \000 :set the value of a field that contains a null
i thought null is already assigned in the previous statement
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
regards
kumar
Hi kumar .. as Lance saidkumar_s wrote:coz even without this i can view data.Nullable (Null Field Value) = \000 :set the value of a field that contains a null
i thought null is already assigned in the previous statement![]()
regards
kumar
#2 makes any error input to be treated as null .. but still it didnt know how to represent that NULL.
#3 takes care of this ... it will keep \000 for each such null value .. so that downstream stages can recognize the null value.