Page 1 of 2
how to transfer nulls as nulls?
Posted: Tue Aug 08, 2006 10:12 am
by mctny
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?
Posted: Tue Aug 08, 2006 11:00 am
by Krazykoolrohit
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
Posted: Tue Aug 08, 2006 11:04 am
by mctny
Krazykoolrohit 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
I use toad to check the table. ans also I tried if IsNull(X) then SetNull() else X
but it didnot help either
Re: how to transfer nulls as nulls?
Posted: Tue Aug 08, 2006 12:58 pm
by kris007
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?
Are you sure you are looking at the same record in source and target?
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.
Re: how to transfer nulls as nulls?
Posted: Tue Aug 08, 2006 1:11 pm
by mctny
kris007 wrote: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?
Are you sure you are looking at the same record in source and target?
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.
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 transformer
, when I read the seq file it is still varchar
Re: how to transfer nulls as nulls?
Posted: Wed Aug 09, 2006 12:59 am
by madhukar
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.
Posted: Wed Aug 09, 2006 1:38 am
by ray.wurlod
Historical Geographical Note
UR is (was) an ancient Babylonian city. There is no such English word.
Posted: Thu Aug 10, 2006 3:47 am
by madhukar
ray,
I regret the inconvenience caused.
correction:
please read "u r" as you are and "ur" your
Posted: Thu Aug 10, 2006 3:58 am
by puli.reddy
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
Posted: Fri Aug 11, 2006 9:24 am
by prabu
Hello folks,
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.
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
any volunteers to exlain @NULL and null value expression
Posted: Sat Aug 12, 2006 1:23 am
by prabu
any volunteers???
Posted: Sat Aug 12, 2006 3:05 am
by kumar_s
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.
Posted: Sat Aug 12, 2006 7:23 am
by mctny
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)
Posted: Sat Aug 12, 2006 10:21 am
by prabu
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.
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,
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.
i think the problem is, it is treating @NULL as literal instead of as a system variable.
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
Posted: Sat Aug 12, 2006 10:31 am
by prabu
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?
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.
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