Remove ALL Nulls from a file

[GLS400]

Hi all:

Is there an easy way of removing all nulls from a file and replaceing them with blanks or zeros?

I have a file with 50+ fields that allow null values.
I also have a case tool which does not allow the h spec...."alwnull(*usrctl)" or "alwnull(*inputonly)"

I'd rather not test and re-set all 50 fields and I'd also prefer not to copy to a non-nulled file (and copy back?).

Any suggestions would be appreciated.


Thanks
GLS

[jamief]

Re: Remove ALL Nulls from a file

Can you use %scanrpl ?

[GLS400]

Re: Remove ALL Nulls from a file

Hi Jamie:

%scanrpl works on a field level....I'm trying to do this on a file level.
I'm not even sure you can scan on the field for null in a null capable field.
I thought you need to use..... if %nullind(myfield)

Thanks for the effort
GLS

[kitvb1]

Re: Remove ALL Nulls from a file

Hi Gary

You can do the following:
1. Declare a cursor and do a multiple row fetch into an ADS

Select column_name from qsys2/syscolumns
WHERE table_name = :MyTable
  and is_nullable = 'Y'

2. Then you can build a dynamic sql statement checking and changing just the fields fetched.

MySqlStmt = 'update mytable set';
For x = 1 to sqler3;
  ChkFld = ArrayFld(x);
  MySqlStmt = %trim(MySqlStmt) + 
     ' (case when ' + q + %trim(ChkFld) + q + ' is null then ' + q + q + ' end)';
EndFor;   

--> run the sucker

p.s. I just typed this off the top in notepad - I haven't checked for syntax and stuff, but it should work, you may need to tweak it.

[kitvb1]

Re: Remove ALL Nulls from a file

p.s. using the same method... instead of doing the update as above, you might rather want to do it as an "alter table" changing the IS_NULLABLE column to 'N'. I don't think that will trigger level check but I am not sure (haven't tried it). Maybe someone more knowledgeable like Barbara can answer you on that.

[B.Hauser]

Re: Remove ALL Nulls from a file

@Kit
The alter table statement, removing the NULLability, cannot be executed successfully as long as there are NULL values within the appropriate column.

When executing the dynamic SQL statement, also the data type of the field should be determined for being able to set the default values correctly, i.e. *Zeros for numeric fields, *Blanks for Character fields, '0001-01-01' for Date Fields, '00.00.00' for time fields etc.

Birgitta

[kitvb1]

Re: Remove ALL Nulls from a file

@biggie...
Yep, I know. I gave only the char field 'cos I'm sure Gary would know just to check the data type and tweak accordingly.
I didn't know that about the ALTER TABLE. I imagined that SQL would be clever enough to change it to the default. But that's also OK. The ALTER TABLE can just be run after the update (if required) using the array.

p.s. Do you know how to change nappies? - for when you in this area next month.

[B.Hauser]

Re: Remove ALL Nulls from a file

Do you know how to change nappies? - for when you in this area next month.

If you learned it, I think I can remember how it worked when I had to change them for my nieces and nephew.

Birgitta

[GLS400]

Re: Remove ALL Nulls from a file

Thanks Kit and Birgitta:

I ended up with copy to non-nulled file and back again.
The cpyf took care of all conversions to default values (alpha numeric or date) so it was 2 cl statements instead of 20-30 sql/rpg statements.

I never figured out how to do nappies...and hope i never will

Thanks again
GLS

[ambusy]

Re: Remove ALL Nulls from a file

UPDATE file set Alfafield = COALESCE(Alfafield,' '), Numfield=COALESCE(Numfield,0), etc

RUNSQLSTM and the job is done

[jamief]

Re: Remove ALL Nulls from a file

@KIT ..I can change your nappies, had no idea that at your young age you have sprung a leak!

I was in Illinois -- freezing my &#$&#$&#$ off! This is a very interesting thread, thanks to everyone.

Jamie

[kitvb1]

Re: Remove ALL Nulls from a file

If you learned it, I think I can remember how it worked when I had to change them for my nieces and nephew.

Birgitta

Sorta like the bicycle thing?... once you fall off a bicycle, you never forget!