COALESCE does not work as DB table always sets 'NOT NULL'

[former_member212705]

Hello Experts,

I have one doubt-

I am trying to use COALESCE function but it does not work

it always returns first value even in table value is blank.

When i checked the table i found "NOT NULL" is "X" in DB, so value is never NULL

to check this i created a table in SE11 , even though i am selecting "initial" flag X or not, DB automatically sets "NOT NULL"

Now i am little bit confused how to deal with this, how to use 'coalesce' function.

If anyone has any idea please clear my doubt.

Thanks in advance

Ashish T.

[horst_keller · Accepted solution]

The NOT_NULL flag is only relevant for inserting new fields into existing database tables.

https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abenddic_database_...

It does not say, that there can't be null values in that field. But normally you simply don't have null values in database tables defined in the dictionary and accessed by ABAP.

Null values can arise as described here:

https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abenopen_sql_null_...

See also the example for COALESCE in Open SQL:

https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abensql_expr_coale...

[former_member212705 · Accepted solution]

Thanks Horst, you cleared my doubt. I got confused with NULL/NOT NULL. Now i got to know how Coalesce works.

[JaySchwendemann · Accepted solution]

How did you end up achieving the wanted result (having passbirth or if that is intial constant 2017/01/01) as birthdate?