false result when select with space

[Former Member]

Hi, I want to select all entries from a DB-table (in my example BUT000) where a customer field ist space. The problem ist, that both the select statement in my program and the request with SE11 come to the same incorrect result.

In my example there are more then 1mio entries in BUT000.

simple coding:

SELECT COUNT(*) FROM but000 INTO cnt

WHERE yfield EQ space.

The type of the customer field is CHAR 1.

Result with yfield EQ space -> 530 entries

Result with yfield NE space -> 0

Normally all entries are space and the correct result should be more the 1 mio.

The results in SE11 and in the smal program are the same. There is no change, when I use ' ' instead of space.

I have this problem only with enhanced customer fields. Selects of standard fields are correct.

After a new activation with DB-utility there is no change.

What is the problem with the customer fields - have anyone an idea? Thanks.

[Former Member]

Hi Steffen,

By any chance, this problem is because of changing the attribute INITIAL VALUES is allowed after putting some records into the Table. Below is the help(F1) on this Check Box in the SE11 by selecting Display.

Indicator that NOT NULL is forced for this field

Select this flag if a field to be inserted in the database is to be

filled with initial values. The initial value used depends on the data

type of the field.

Please note that fields in the database for which the this flag is not

set can also be filled with initial values.

When you create a table, all fields of the table can be defined as NOT

NULL and filled with an initial value. The same applies when converting

the table. Only when new fields are added or inserted, are these filled

with initial values. An exception is key fields. These are always filled

automatically with initial values.

Restrictions and notes:

The initial value cannot be set for fields of data types LCHR, LRAW,

and RAW. If the field length is greater than 32, the initial flag

cannot be set for fields of data type NUMC.

If a new field is inserted in the table and the initial flag is set,

the complete table is scanned on activation and an UPDATE is made to

the new field. This can be very time-consuming.

If the initial flag is set for an included structure, this means

that the attributes from the structure are transferred. That is,

exactly those fields which are marked as initial in the definition

have this attribute in the table as well.

Regards,

Satya

[Former Member]

Your test is available only if your field is type char. If even yes, it's quite strange, if even no, you should change your test to > 0.

Christophe

[Former Member]

Hi,

surely Your customer field is later added. And maybe Your database does not initialize newly added tablefields. This could lead to the value NULL (undefined) in unhandled customer field with unpredictible effects.

Choose the initialize-option for your customer field!

Good luck!

Jo

[Former Member]

Hi Steffen,

By any chance, this problem is because of changing the attribute INITIAL VALUES is allowed after putting some records into the Table. Below is the help(F1) on this Check Box in the SE11 by selecting Display.

Indicator that NOT NULL is forced for this field

Select this flag if a field to be inserted in the database is to be

filled with initial values. The initial value used depends on the data

type of the field.

Please note that fields in the database for which the this flag is not

set can also be filled with initial values.

When you create a table, all fields of the table can be defined as NOT

NULL and filled with an initial value. The same applies when converting

the table. Only when new fields are added or inserted, are these filled

with initial values. An exception is key fields. These are always filled

automatically with initial values.

Restrictions and notes:

The initial value cannot be set for fields of data types LCHR, LRAW,

and RAW. If the field length is greater than 32, the initial flag

cannot be set for fields of data type NUMC.

If a new field is inserted in the table and the initial flag is set,

the complete table is scanned on activation and an UPDATE is made to

the new field. This can be very time-consuming.

If the initial flag is set for an included structure, this means

that the attributes from the structure are transferred. That is,

exactly those fields which are marked as initial in the definition

have this attribute in the table as well.

Regards,

Satya

[Former Member]

Thank you, that solves my problem.

[Former Member]

Hello All, I have another problem with INITIAL VALUES. I have added new fields (char) in a append structure witch is included in an existing standard database table. The INITIAL-Flag Iu2019ve to activate in the append structure. After that I activate the structure and automatically with it the depending tables where the structure is included. I awaited, that the INITIAL-Flags from the structure are transferred in the table and there is a correct initial state on the database. But there is nothing. Neither are the flags in the table activated nor is the state in the database correct. In the append structure directly the INITIAL-Flags are still activated. A separate activation about database-utilities didnu2019t solve the problem. Knows anybody, where the problem is?

[Former Member]

Hi,

When you select for specific fields see to it that all the fields in the primary key are selected.

else duplicate entries will not come and there will be difference in the number of records.

Award points if helpful