sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Replace NULL before Inserting into File ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Replace NULL before Inserting into File ?

    I am modifying a QRPGLESRC program with embedded SQL. It's inserting into a file that doesn't allow NULLs. What is the IBMi equivalent of ISNULL to check for a null value, so I can replace with a 0 or blank ?

    EDIT: Looks like IFNULL is what I want
    Last edited by MFisher; July 29th, 2020, 07:45 AM.

  • #2
    I use COALESCE().

    Comment


    • #3
      COALESCE and IFNULL convert both a NULL value into a default value, but COALESCE is more powerful, because we can specify multiple option, while IFNULL must be nested.
      Example:

      Code:
      Select Coalesce(a.CustNo, b.CustNo, c.CustNo) Customer1,
             IFNull(IFNull(a.CustNo, B.CustNo), c.CustNo) Customer2  
            .....
         from Sales2018 a full join Sales2019 b on a.CustNo = b.CustNo
                          full join Sales2020 c on a.CustNo = c.CustNo
         .....
      Birgitta

      Comment

      Working...
      X