writing Null field on a fixed width sequential file

[dsedi]

All,

Before posting this actually i did a little search on the available forums and tried the something on the Job.
But nothing is success full,

We need to write a Null field value(Nullable = yes) on a fixed width sequential file.i.e
if col1 is varchar(10) then I hope i can give Null field value property
as ' '(10 spaces).
but if i have more columns or the length is more for each columns
then this will be a overhead.

Anybody have an idea on this?

While searching i found some patch like APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL
is this necessary to achieve this?

thanks in advance,Edi

[DSguru2B]

Specify it as char instead of varchar. Specify the length's properly. Set the APT_PAD_CHARACTER to space " ".

[dsedi]

Thanks!

what if the incoming field data type will be like decimal (length 14 scale 2) ???

Thanks again,Edi

[DSguru2B]

Specify that as char as well. A fixed width flat file should contain all character fields to honor the length.

[dsedi]

SOrry DSguru!

JOb failed saying like

"liability_column_rename: Error when checking operator: When binding output schema variable "outRec": When binding output interface field "OID" to field "LIABILITY_OID": Implicit conversion from source type "decimal[31,0]" to result type "string[31]": Not enough room in string for decimal[31,0]. [api/interface_rep.C:6257]"

Any idea?

FYI,now I haven't specify any of the "Edit columns" Property.

Thanks,Edi

[kumar_s]

You are tyring to do an implicit conversion from decimal to char / varchar.
You can try an explict conversion.
But what will the file used after it been created?
Can decimal field be filled with space?

[dsedi]

Kumar,

I am just doing a direct writing into a file
file should be fixed width (no delimiter required)

i/p example

Code: Select all

Record 1 
col1(decimal 5) = 1234 & col2varchar(4) = abcd & col3 decimal(2) = 12
record 2
col1(decimal 5) = 4568 & col2varchar(4) = Null & col3 decimal(2) = 78
record 3
col1(decimal 5) = 0345 & col2varchar(4) = wxyz & col3 decimal(2) = Null

then output in fixed width file should be like

Code: Select all

1234abcd12
4568    78
0345wxyz  

Hope this could explain the need better.

thanks,Edi

[DSguru2B]

Well when I said 'Specify it as char', I meant specify it as char and take care of the explicit conversion. On second thought, if you leave the decimal values as decimal and specify the fill character as 0 you should be fine. This way for decimal(4) if you get 123, you will actually write 0123 to the file. You should be able to find this property in extended properties.

[dsedi]

Thanks DS!

I am just writing the data onto a sequential file!
if i neeed to do an explicit conv then i need a transformer which i can't use!
need to write the buidop code for that.
so i am trying to avoid transformer.

so.the final answer is if i want to write the decimal field which may have null value into a fixed length file then we should be specifying the nul field value as the spaces like ' '.
is that right?

thanks,edi

[DSguru2B]

Try with that, I dont know if you will get four spaces. Thats why I advised to use char in the first place. I know for sure with char you will get four spaces. You can also use modify stage for explicit data type conversions.
Otherwise you can specify x number of zeros where x is the length of your decimal field.

[dsedi]

sorry Ds!

I mean to say if col1 length of 5 then
null field value property like'five spaces'

the reason for not using modify is
it's breaking the metadata mapping & also i need to rename most of the columns.so for each and every column,i need to write a specification.

anyway..thanks for your time DS.

Kumar any thoughts?

thanks,Edi

[kumar_s]

What is your current job design (What is your source)?
Why can't you use Transformer?

[dsedi]

What is your current job design (What is your source)?
Why can't you use Transformer? ...

I am reading data from a dataset
Renaming the columns
Writing on to the fixed width sequential file (no delimiter)

Transformer!! ?? I dono how i am going to write buidop code for all transformation rules...thinking about convince my Lead!

Thanks, Edi

[kumar_s]

Dont you have Transformer stage?

[DSguru2B]

.....or even a Modify stage? Datatype conversions can also be made in a modify stage. And who told you that you need to write a buildop for that