Nulls in Pipe delimited file

patonp · Post by **patonp** » Tue Jan 30, 2007 7:38 am

Simple question....

I need to generate a pipe delimited sequential file where NULLS are represented by having no data between two delimiters regardless of the data type.

For example, if I have three fields in a file definition, and only the first field has a value for a given record, this would be represented as:

test||

Can I do this on the parallel canvas?

Thanks!

Peter

DSguru2B · Post by **DSguru2B** » Tue Jan 30, 2007 7:52 am

Sure you can. Do a Null check on the incoming record and if Null is found, change it to an empty character.

patonp · Post by **patonp** » Tue Jan 30, 2007 9:52 am

I was wondering about data types like dates or decimals. I don't seem to be able to represent these using the method you describe.

DSguru2B · Post by **DSguru2B** » Tue Jan 30, 2007 10:50 am

They cannot be represented as empty characters. They have to be changed to string first and then filled with an empty space. If you would like to retain their type then put the appropriate value. Like say 0 for integer and 0.00 for Decimal (3,2).

patonp · Post by **patonp** » Tue Jan 30, 2007 1:56 pm

DSguru2B wrote:They cannot be represented as empty characters. .

Is there really no way to do this? I'd like to replace an existing process that produces an output file with NULL dates represented by adjacent delimiters rather than a substituted value representing NULL.

DSguru2B · Post by **DSguru2B** » Tue Jan 30, 2007 1:59 pm

They can be if you make the target as varchar.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 30, 2007 3:31 pm

If you set the Null Field Value property to "" you will get adjacent delimiters in your output file.

patonp · Post by **patonp** » Wed Jan 31, 2007 7:33 am

ray.wurlod wrote:If you set the Null Field Value property to "" you will get adjacent delimiters in your output file.

This only seems to work for certain datatypes. What if my inbound delimited source file represents Null dates using adjacent delimiters? When I set the Null Field Value to "", I receive an error stating "A fixed length field cannot have a "null_field" of length 0 on import".

ray.wurlod · Post by **ray.wurlod** » Wed Jan 31, 2007 3:36 pm

True. But someone had already posted that you can specify VarChar. Then all will be well. After all, there are no "real" data types in text files, only text.