topic How to replace NULL value in a table after creation in Application Development and Automation Discussions

How to replace NULL value in a table after creation

Former Member — Thu, 21 Jan 2010 16:02:18 GMT

Dear all,

We added an include to the table EKKO containing several fields including ZZTYPC.

The initialization flag was not set at creation time so the new fields were populated with the NULL value.

We would like now to initialize the value for all the records of the field ZZTYPC to avoid problems when we use this field as a selection criteria in Tx SE16.

As suggested in the following post

I checked the initilization flag for this field at the structure level (Dictionary : Display structure), save & activate but the modification is not carried over the table EKKO : the "initial values" check box stays unchecked for the field ZZTYPC thus the adaptation of EKKO is useless.

We use DB2 as database.

You help would be appreciated.

Best Regards,

Olivier

Re: How to replace NULL value in a table after creation

Former Member — Fri, 22 Jan 2010 06:36:30 GMT

Hi Olivier,

Please try to put values in all the records of ZTYPC field. Once all the records are filled with some values apart from null, then try to check the initialization check box in Maintain table screen.

Hope this will work.

Re: How to replace NULL value in a table after creation

Former Member — Thu, 05 May 2011 14:06:45 GMT

I just ran into this problem as well. I added an append structure to REGUH, you can not check the initial value column.

What I found was the new field on existing records contained a null value and on new records it was set to initial.

What I did (in test) is write a program that changes all records where the new field = NULL to inital date.

DATA: init_date LIKE reguh-zzextrd,

init_time LIKE reguh-zzextrt.

CLEAR: init_date,

init_time.

UPDATE reguh

SET zzextrd = init_date

zzextrt = init_time

WHERE zzextrd IS NULL.

Jerry

Re: How to replace NULL value in a table after creation

VEPS — Thu, 05 May 2011 16:01:53 GMT

Hi Olivier,

Similarly you could use the following to update all NULL values to spaces using SQL etc...

update ZTABLE set ZFIELD = ' ' where ZFIELD is null

Best regards,

-Derrick

Re: How to replace NULL value in a table after creation

brad_bohn — Thu, 05 May 2011 18:22:54 GMT


 I just ran into this problem as well. I added an append structure to REGUH, you can not check the initial value column.

@Jerry: That's not true. You can always check the box for a DB field. When you use an append structure in a DB table, by default the Initialization flag is not shown. You simply need to go to Extras->DB Attributes -> Init flag On/Off. This removes the need to use a program to fix the issue for new records.

Re: How to replace NULL value in a table after creation

Venkat_Sesha — Fri, 20 May 2011 08:33:19 GMT

Hi,

Do one thing. Change the table and now check the Initialize flag and the go to database Utility and check the radio button Save and Adjust and then activate the table. You will not lose the data.

Thnx,

Bhargav.

Re: How to replace NULL value in a table after creation

Former Member — Fri, 20 May 2011 12:59:01 GMT

Hi Olivier,

Please change the INIT Flag within the Extras TAB and then goto SE14 to adjust the database table.

Though updating the Table with changes in the value NULL is easy to do it, sometimes the ztable cannot be adjusted automatically which leads to data inconsistency.

Revert for further clarification

Thanks

Sri

Re: How to replace NULL value in a table after creation

Former Member — Fri, 10 Jun 2011 10:35:25 GMT

HI,

Go to SE14 give the table name , Select Extras->Force conversion that initiates all the null Values...

Regards,

Deepak.

Re: How to replace NULL value in a table after creation

Former Member — Mon, 18 Jul 2011 10:16:54 GMT

Hi Deepak,

I have added an append structure to table MARC with several CHAR1 fields for checkboxes.

Our development system is running on OS: Linux/DB: Oracle and has no problems in SE16 filtering on these new fields.

After transporting the append structure to a customer system running on OS: Microsoft Server 2008/DB: MS SQL Server, transaction SE16 was behaving unexpected as selection on the new fields were returning not all expected records.

MS SQL Server handles NULL values differently than Oracle.

You gave me the solution for our problem:

transaction SE14 > table MARC > Select Extras >Force conversion that initiates all the null Values

This action has to be repeated in every system at the customer (development, test and production).

Thank you very much for sharing your solution.

NOTE: it is impossible to set the Initial value flag in a table for fields added via an append structure.

Edited by: R. J. Bouwknegt on Jul 18, 2011 12:17 PM