how to transfer nulls as nulls?

[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?

[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

[mctny]

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

[kris007]

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.

[mctny]

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

[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.

[ray.wurlod]

Historical Geographical Note
UR is (was) an ancient Babylonian city. There is no such English word.

[madhukar]

ray,

I regret the inconvenience caused.

correction:

please read "u r" as you are and "ur" your

[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

[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

[prabu]

any volunteers???

[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.

[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)

[prabu]

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

[prabu]

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