how to transfer nulls as nulls?
Moderators: chulett, rschirm, roy
how to transfer nulls as nulls?
I have a simple but big problem I have a sequential file as input a transformer and oracle enterprise stage as target. I am using delimited flat file and some fields are nullable, and I want to transfer those fields as null if it is null. I read one col as varchar length 7 this column is number(7,4) in oracle. when I load the target table I see that the nulls at the input are transferred as zero, I couldnot make it null no matter what I tried. any ideas?
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
I use toad to check the table. ans also I tried if IsNull(X) then SetNull() else XKrazykoolrohit wrote:Strange. I dont think that datastage is the culprit. Check if default display for nulls in oracle is zero. (i assume you are running a query in oracle to check)
Otherwise Try and force null into the field.
If isnull(XYZ) then NULL() else XYZ
but it didnot help either
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Re: how to transfer nulls as nulls?
Are you sure you are looking at the same record in source and target?mctny wrote: when I load the target table I see that the nulls at the input are transferred as zero, I couldnot make it null no matter what I tried. any ideas?
You get 0's when you move from varchar to NUmber field if it has any non-numeric data.Make sure you are handling null values correctly in the seq file stage.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Re: how to transfer nulls as nulls?
thank you for the response, yes I am sure I am looking at the right records. I solved the problem by using unknown field type at the right side of transformerkris007 wrote:Are you sure you are looking at the same record in source and target?mctny wrote: when I load the target table I see that the nulls at the input are transferred as zero, I couldnot make it null no matter what I tried. any ideas?
You get 0's when you move from varchar to NUmber field if it has any non-numeric data.Make sure you are handling null values correctly in the seq file stage.
, when I read the seq file it is still varchar
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Re: how to transfer nulls as nulls?
Check the nullable property in input & output of transformer.
if u r not doing any explicit tranformation of converting varachar to number(7,4) in transformer then, it does implicit conversion.
if ur varchar column does not have proper number, it will put zero as default.
try an explicit conversion in transformer.
if u r not doing any explicit tranformation of converting varachar to number(7,4) in transformer then, it does implicit conversion.
if ur varchar column does not have proper number, it will put zero as default.
try an explicit conversion in transformer.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 28
- Joined: Wed Jan 11, 2006 8:29 pm
- Location: Singapore
- Contact:
Hi All
I had the same problem, I used @NULL function to hardcode the values as null. It doesn't work when we are populting into Oracle. Try the below
If IsNull(Arg1) = 1 Then '' Else Arg1. It may work out. I had this problem when I was populating into Oracle8i database
I had the same problem, I used @NULL function to hardcode the values as null. It doesn't work when we are populting into Oracle. Try the below
If IsNull(Arg1) = 1 Then '' Else Arg1. It may work out. I had this problem when I was populating into Oracle8i database
Thnaks and Regards,
AnjiReddy.
AnjiReddy.
Hello folks,
the following is the hint for the null value property of sequential file.
unfortunately, @NULL or @NULL.STR , which are "system variables" are not considered for the above said propety.
1)question is, if i have Oracle Enterprise stage mapped directly to a seq file, how to handle the nulls as NULLS.
i can understand if it is
Stage -> Transformer -> Seq file
i can use null handling function like SetNull.
any volunteers
the following is the hint for the null value property of sequential file.
Code: Select all
On import, the [b]value NOT expression NOT system variable [/b] given to a field containing a null. On export, the value given to an exported field if the source field is set to null.
May be a number, string, or C-style literal escape character, such as \123 or \xAB, if you want to use a non-printable byte value.
1)question is, if i have Oracle Enterprise stage mapped directly to a seq file, how to handle the nulls as NULLS.
i can understand if it is
Stage -> Transformer -> Seq file
i can use null handling function like SetNull.
any volunteers
any volunteers to exlain @NULL and null value expression
any volunteers???
Prabu - What if you dont use any of these properties. Null should be transfered as Null. Are you getting the same issue as Cetin getting?
Cetin - Try to load in to sequential file and check if you are getting 0s.
Cetin - Try to load in to sequential file and check if you are getting 0s.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Hi Kumar,
I agree with you. Nulls should be tansferred as nulls if you don't do anything.
I solved the problem using unknown data type in the right side of the mapping of the transformer. I have to use transformer anyway, because of other fields ransformation.
as far as I remember, when I view the data at the source stage( which is sequential) I can view nulls as nulls. I remember dumping the data to sequntial file to test, and it was also fine.
I think in my case it was the transformer that cause the problem ( i.e. automatically converting nulls to zero due to the field type)
I agree with you. Nulls should be tansferred as nulls if you don't do anything.
I solved the problem using unknown data type in the right side of the mapping of the transformer. I have to use transformer anyway, because of other fields ransformation.
as far as I remember, when I view the data at the source stage( which is sequential) I can view nulls as nulls. I remember dumping the data to sequntial file to test, and it was also fine.
I think in my case it was the transformer that cause the problem ( i.e. automatically converting nulls to zero due to the field type)
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Cetin, my question is,if its a straight mapping between the source(oracle table) and target(sequential file) how would i handle it. To restate,mctny wrote:Hi Kumar,
I agree with you. Nulls should be tansferred as nulls if you don't do anything.
I solved the problem using unknown data type in the right side of the mapping of the transformer. I have to use transformer anyway, because of other fields ransformation.
i think the problem is, it is treating @NULL as literal instead of as a system variable.unfortunately, @NULL or @NULL.STR , which are "system variables" are not considered for the above said propetyThe problem is null value property is not taking @NULL as a value.
My question is:-
1)
if there is NO transformation inloved, is there no way to write nulls as null itself into a sequemtial file.
2) how can i use system variables as property value [as discussed for null value - @NULL]. is it allowed?
looking for some clues??
regards,
Prabu
it's not allowing me to load the seq file, if the field value is null and the null value property is not set. Meaning, these null records are rejected.kumar_s wrote:Prabu - What if you dont use any of these properties. Null should be transfered as Null. Are you getting the same issue as Cetin getting?
i assume null value is a prerequisite provided the input field may contain nulls.
the mock log messages for your reference.
Code: Select all
"COL" is null but no null export handling is defined
Export unsuccessful at record n