ORA-01407 with UPDATE using COALESCE function
Hi
We're trying to run the following query as part of an upgrade:
UPDATE LAWSON.NATBALANCE
SET STATEMENT_REQ=(SELECT COALESCE(acm.STATEMENT_REQ,' ')
FROM LAWSON.ARCUSTOMER acm
WHERE acm.NAT_FLAG='N'
AND LAWSON.NATBALANCE.NAT_COMPANY=acm.COMPANY AND
LAWSON.NATBALANCE.NAT_CUSTOMER=acm.CUSTOMER)
But we're getting the following error:
ORA-01407: cannot update ("LAWSON"."NATBALANCE"."STATEMENT_REQ") to null.
I’m confused by this for a couple of reasons. First, the STATEMENT_REQ field in LAWSON.ARCUSTOMER contains no NULLS, and second I thought the whole purpose of the COALESCE function was to return the first non-null result. What am I missing here?
Edited by: ltzwoman on Dec 4, 2012 10:48 AM