Friday, June 24, 2016

COALESCE function in DB2 . Alternative to NULL indicator variable in DB2 ; SQLCODE - 305.

If a table column is 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.
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;
This function can be used in many ways to get desired results. 

2 comments:

  1. Waiting for more new posts...

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly Contact MaxMunus
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 1,00,000 + trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+918553576305
    www.MaxMunus.com


    ReplyDelete