If a table column in not defined with NOT NULL, then it implies that the column value can be NULL sometimes. For such columns,if we do not use null handling technique, then the application program will give sqlcode -305.
To avoid that we use indicator variables.However , we can also use the DB2 COALESCE function.
COALESCE is a function that allows you to substitute any numeric or character value for a null, based on the column data type.
Suppose in DEPT table, i have one column for optional subject(optsubj). It can have a value of 'Y'or 'N'. However since the column is not defined with NOT NULL keyword,it can fetch null values.
If we simply use the query below, our application program will return sqlcode -305.
Related Articles
SELECT fname
,lname
,optsubj
into :hv-fname
:hv-lname
:hv-optsubj
from DEPT
However, we can tweak the same query in the below way using COALESCE function to get rid of -305 without using indicator variable
SELECT fname
,lname
,coalesce(optsubj,space(1))
into :hv-fname
:hv-lname
:hv-optsubj
from DEPT
What it will do is, it will replace NULL value with space of 1 byte whenever the column optsubj fetches a null value for any record.