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
Handling nulls in seq file...
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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...
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...
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
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.
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.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
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.kandyshandy wrote: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...
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.
I use the following condition in transformer.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
If file1.amt1 <> file2.amt1 then file1.amt1 else SetNull()