Search code examples
selectinformix

informix 14.10 How to "select" returns a specific phrase such as None or blank instead of no result

I have a query like this:

  select c1 , ( select d1 from table2 where dt) from table1 where  ct

but if there is no d1 under condition dt i have no result but i have a reult like this:

--c1               d1
value1       NONE or  Blank
value 2      NONE or  Blank    
  .                .
  .                .

Can anybody Help?

Solution

  • The NVL function can be used to return either of its two arguments depending on whether the first evaluates to NULL. So your example query could be written as:

    select c1 , NVL(( select d1 from table2 where dt), "NONE") from table1 where ct

    The data types of the two arguments need to be compatible, for example both character or both numeric. More information can be found at https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_1445.htm