Handling nulls in seq file...

[kaps]

I am having problem handling nulls when I write to a seq file stage.
It's a decimal column and I made it as nullable and in the Null Field Value I put 14 spaces as the length is 12 and precision is 3. But I see 0's in the output file.

Two queastions.

1. Is there a better way than typing 14 nulls in the column ?
2. I don't want to see 0's in the output file. I just wanted it to be empty.

Please advise.

Thanks

[nani0907]

hello,

Convert the decimaltype to varchar to avoid the zeor's

[kaps]

It does not work even If I covert it to Varchar. I still see 0's in the file.

[kandyshandy]

Expect 14 spaces in a decimal field? It will not allow that. it will show 000000.00 for the specified precision. You may want to convert the target field to string to get empty instead of 0s

NULL will be NULL in varchar field. It will not become 0s unless it is specifically converted.

Tell us the source field defintion, the value, transformation and the target field defintion...

[FranklinE]

I'm currently doing something similar, as follows:

Input field varies from integer to string. I convert it to string (VarChar) and in the derivation I put an "empty string" if I want it to be empty instead of zero, space or null. I use stage variables in the transformer for all the intermediate steps.

I solved the empty string issue by defining it in the job properties parameters tab. I just named it and avoid giving it a value there or at any other point.

[kaps]

Expect 14 spaces in a decimal field? It will not allow that. it will show 000000.00 for the specified precision. You may want to convert the target field to string to get empty instead of 0s

NULL will be NULL in varchar field. It will not become 0s unless it is specifically converted.

Tell us the source field defintion, the value, transformation and the target field defintion...

No I am not expecting 14 spaces. Problem is that since it's nullable field I need to specify the null field value and it does not accept anything less than 14 spaces. I am worried about this as if the width is 30 then I have to put 30 spaces. I don't know a way to work around it.

Source field definition is Decimal with the length of 12 and scale of 3.
Target field definition is Decimal with the length of 12 and scale of 3. I tried changing this to varchar and still gettong o's.

Basically I am comparing two sets of amount from two seq files and want to dispaly if they are differnet.

Foe Example...Input is as shown below...

File1: 01,100.00,110.00

File2: 01,100.00,120.00

Following is the output I get:

01,0.00,110.00,01,0.00,120


Following is the output I want:

01,SPACES,110.00,01,SPACES,120

I use the following condition in transformer.

If file1.amt1 <> file2.amt1 then file1.amt1 else SetNull()

[nitkuar]

you need to make the column nullable while reading data from source itself. Could you please check nullability of the column in source stage?

[kaps]

It is nullable in Source stage.