NULL and Space value in ABAP

[Former Member]

Hi All,

I like to know, is it NULL and Space value is same in ABAP, if it is not how to check null value.

Thank you.

Senthil

[suresh_datti]

Hi Senthil,

You check the null value using INITIAL..

IF NOT w_matnr IS INITIAL.

or

IF w_matnr IS INITIAL.

Regards,

Suresh Datti

[Former Member]

Dear Senthil,

NULL of oracle is reffered as INITIAL in ABAP. As such INITIAL and '' have same meaning. If you want to specifically check with space then you use SPACE reserved word.

Regards,

Deva.

[Former Member]

Hello Senthil,

the above replies are not correct. A NULL value on Oracle level is something completely different from the SPACE value on SAP side. In SAP environments NOT NULL constraints are often used, so NULL values should not appear so often. But under certain conditions (e.g. in order to avoid time-consuming conversions when adding a column to a table) the NOT NULL constraint is omitted by the SAP DDIC. In this case a column can contain both NULL and SPACE values. In order to capture both you have to select for both:

<column> = ' ' or <column> IS NULL

If you forget the second part, the result set is not complete. See also note 617679.

Regards

Martin Frauendorfer

SAP Active Global Support - Technology

[tsenol]

Hello Martin,

We have a AIX/Oracle system and will migrate it to Win/Oracle.

In our system there are a few custom tables that have NULL columns and a oracle level program controls this NULL values.

After we migrated the system We saw that NULL columns changed to NOT NULL values with initial value and our oracle level program is broken.

1- Is it mandotary for SAP to change this NULL values to NOT NULL ?

2- Can I change this NOT NULL columns for a specified table to NULL in .STR or .SQL files ?

Regards

Tutku

[Former Member]

Hello Senthil,

I think I was to fast saying that the above answers are not correct, sorry. They are also valid. But nevertheless you may also consider the information I wrote in my above reply.

Regards

Martin

[Clemenss]

everything is correct though some answers are not correct.

A Database NULL value represents a field that has never been stored to database - this saving space, potentially.

Usually all SAP tables are stored with all fields, empty fields are stored with their initial value.

But: If a new table append is created and the newly-added fields do not have the 'initial value' marked in table definition, Oracle will just set NULL values for them.

as mentioned: There is no NULL value to be stored in an ABAP field. The IS NULL comparison is valid only for WHERE clause in SELECT statement. WHERE field = space is different from WHERE field IS NULL. That's why you should check for both specially for appended table fields.

If a record is selected (fulfilling another WHERE condition) into an internal table or work area, NULL values are convertted to their initial values anyway.

Hope that sheds some light on the subject!

regards,

Clemens

[Former Member]

hey can i guess the answer!!! ia ma just a beginer, but then what i know is when making tables in abap if u check the initial indicator it transfers null value to the data base that is : eg: suppose u r using abap with oracle database the initial value of for eg: type n here(abap) is 00000 ... however in oracle it mite be different......, so if u uinsert null values through abap it takes the initial values of the database else if u put nothing in aba it takes the initial value of abap ...!!!!! comments needed!!!!

[Clemenss]

yes, you guess quite right.

A NULL value in the database means that ORACLE will not use physical storage space for the value. NULL means there is nothing, not even an initial value stored.

If ABAP inserts a structured database record, all fields are populated in the database, at least storing the initial value for a field. I don't even know the way to store NULL values using ABAP open SQL statements.

An unchanged standard SAP dictionary table should not carry any NULL values because always the whole structured record is transfered to the database.

But because you can never know for sure, a selection for unpopulated fields should compare for the initial value OR space OR NULL.

When writing to the database (insert or update), just don't care.

regards,

Clemens

[raj_p]

I have a similar problem. I have a requirement to update external database table field either with a NULL value or non-zero numeric value. iam thinking of using either EXEC SQL statement or a SAP view to that table (using databaselink). The field is a numeric field in that table.

I tried to update that table intwo ways using ABAP program. If i use CHAR field type it's passing 'space ' which is not a valid value.If i use numeric field it's defalting to zero value,again,which is not a valid value.

How can i update that external table field with NULL value.Can anybody advice ?

Is any bettwer idea available other than using PL/SQL objects at databaselevl !!

Thank you

Raj

[Former Member]

it WORKS!!!!!!!!!!!!!! THANK YOU SO MUCH!!!!!!!!!!!!!!!

i am a beginner who starts a week ago. i am SO NOOB now hahaha.

feel damn happy when it works! THANKS ALOT!!!!!!!!! btw are u still doing abap??

[Former Member]

You can use next sentence

SELECT FROM DB

WHERE filed IS NULL.