topic NULL vs INITIAL in Application Development and Automation Discussions

NULL vs INITIAL

Former Member — Wed, 06 Dec 2006 21:40:01 GMT

Hi all.

Here is my performance dilemma.

Is there any difference between these two lines of code?

SELECT * FROM /abc/def INTO TABLE lt_abc WHERE my_var IS NOT NULL.

SELECT * FROM /abc/def INTO TABLE lt_abc WHERE my_var NE ''.

I get a performance error if NULL is used. Would second line be a good alternative?

Thank you.

Re: NULL vs INITIAL

ferry_lianto — Wed, 06 Dec 2006 21:41:22 GMT

Hi,

Second select statement is better.

Regards,

Ferry Lianto

Re: NULL vs INITIAL

Former Member — Wed, 06 Dec 2006 21:44:52 GMT

Hi Ferry,

Is that the same though?

Thanks.

Re: NULL vs INITIAL

Former Member — Wed, 06 Dec 2006 21:46:56 GMT

From the help:


Variant 7 
f IS [NOT] NULL 

Effect 
The condition is true if the contents of table field f 
(does not) contain(s) the NULL value. 

Example 
Example to select all customers for whom no telephone 
number is specified: 

DATA WA_SCUSTOM TYPE SCUSTOM. 

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE IS NULL. 
ENDSELECT. 

Note 
Performance: 

The SAP buffer does not support this variant. Therefore, 
every SELECT command on a buffered table or a view with 
fields from buffered table that contains ... WHERE f IS 
[NOT] NULL behaves as though the FROM clause contained 
the BYPASSING BUFFER addition.

Rob

Message was edited by:

Rob Burbank

Re: NULL vs INITIAL

Clemenss — Wed, 06 Dec 2006 22:16:07 GMT

Hi,

usually there are no NULL values stored for any field in any table.

NULL values for a field means that physically nothing is stored. This only happend when a new table field is appended for an existing database table. If not explicitly defined as 'INITIAL' the database appends this field and the no value for any record is stored - this is NULL.

For NE '' you can say NE SPACE.

If you have to care for possible NULL values it must be WHERE my_var NE SPACE and WHERE my_var IS NOT NULL.

The bad thing about NULL values is that you can not distinguish them from INITIAL values because the NULL value is converted to initial.

Try once SELECT COUNT( * ) FROM /abc/def where my_var IS NULL.

If any records are found, then you have to care about NULL values. Probably you won't.

Regards,

Clemens