ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Handling NULL value on Update

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

  • Handling NULL value on Update

    I'm updating several values in an existing table that does not allow NULL values. This is the "abridged" SQL statement (embedded in RPG).

    Code:
           Update MYORDERS set
           BOOK_MTD = (select sum(ord_amt) from ORDERS where 
             ord_date>=:beginMonth and ord_date<=:endDate) ,
           BOOK_YTD = (select sum(ord_amt) from ORDERS where 
              ord_date>=:beginYear and ord_date<=:endDate),
           Where YEAR = 2019;
    If the SUM(ord_amt) is NULL, the update fails with Sql State: 23502 Sql Code: -407

    Is there a way to specify an update value of zero(0) instead of NULL?


  • #2
    Found it...

    Coalesce(sum(ord_amt),0)

    Comment


    • #3
      Originally posted by gwilburn View Post
      Found it...

      Coalesce(sum(ord_amt),0)
      ifNull() also works.

      Comment

      Working...
      X