Replace Spaces occuring between ? & | with X'00'

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Hi,
I have an I/p file whose fields are delimited by '|'. This file is unloaded from a table which has nullable fields. So if a particular field has null value
in the table, the corresponding value of it in file after download is spaces
with Null indicator(?) at end of that field.

My task requires to replace the spaces of these fields with X'00' . Please note that the spaces of Nullable fields only need to be replaced and there
will be no spaces /null character in the last field as it contains System date.

Eg:
21|asc| ?| | ?|25-04-2008

Need to replace the spaces of 3rd and 5th field only.
The requirement in a nutshell is how do i replace spaces between ? and |.

saiprasadh · Active User Joined: 20 Sep 2006 Posts: 154 Location: US

Hi Puspojit,

Try this below Sort Card

dbzTHEdinosauer · Posted: Sat Apr 26, 2008 4:25 pm

why don't you use a CASE in the unload to populate with zero when null?

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Hi Sai,

Thanks for the sort card....it works fine but it would not give the desired result for those nullable fields which have spaces as their value with Null Indicator set off.

Eg : 1|asc| ?| | ?|2500000100
1|asc| | | |2500000100

My requirement is that only the third and fifth field of first record be replaced with X'00' as it has Null Indicator(?) but the second record should not get replaced with X'00' as Null indicator is absent there.

Thus I think there needs to be a condition that shall check that the spaces are between | and ? before replacing them......Please correct me if I am wrong......

Thanks and Regards.

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Hi Dick,

I have not used CASE over here because I want to replace the whole length of 50 bytes(say) for a Nullable field with 50 X'00'....Now this is only possible if I write X'00' 50 times because I guess I cant use the Padding option in the Case statement .....Please give me an example if you think it is possible with the case statement .....I had used the below statement

CASE WHEN NME_MIDDLE IS NULL THEN X'00'
ELSE NME_MIDDLE END

This would fill only one X'00' for that column, the rest shall contain spaces......My requirement is that all the 50 bytes for this field should
be filled with X'00'.....I hope I am clear with my requirement

Thanks in advance....

enrico-sorichetti · Posted: Sat Apr 26, 2008 7:05 pm

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Hi Enrico Sorichetti ......

I tried this statement for my requirement

CASE WHEN NME_MIDDLE IS NULL THEN REPEAT(X'00',50)
ELSE NME_MIDDLE END

It works fine but I do not understand how do I use the same statement for a Decimal/date column which are nullable ? Here it works fine as this column is declared as Varchar. Please note that I want to move only Low values(to all columns. irrespective of their types) to the fields having null value in table.

I got my answer by using Case statement(for char fields) and the sort card(for numeric/date fields) given by Sai....Its a bit lenghthy indeed but gives me the desired results.....Please let me know whether there is a shorter and smarter approach

Regards,
Puspojit.

Skolusu · Posted: Mon Apr 28, 2008 11:38 pm

Puspojit,

If you have used DSNTIAUL program to unload the records from the table, all nullable columns are unloaded with X'00' and there is no need for you to change anything.

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Thanks for the info.....I will try the same .

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Would there be any performance issue if I use DSNTIAUL instead of
INZUTILB ?

Regards,
Puspojit

saiprasadh · Active User Joined: 20 Sep 2006 Posts: 154 Location: US

Hi Puspojit,

Try the below Sort Card

Skolusu · Posted: Tue Apr 29, 2008 9:04 pm

Puspojit,

INZUTILB also can pad binary zeroes for null columns and it performs much better than DSNTIAUL.

Saiprasadh,

You are only considering 2 columns with null values. and OP seems to have more than that. Aslo you are only padding 1 byte with binary zeros where as OP wants all the bytes of null able columns to be padded with binary zeros.

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune