When you break your statement into pieces you have: update kate/f56525 A set AQ$PLU = (***) where AQ$DKY = 3 and Aq$DKM > 8 where those "***" are is a select statement. If the value between those parens is null, you have a problem. So we'll look at that statement: select Value(XA$PLU,0) from jbbidata/f57600 B where A.AQ$RCV = B.XA$RCV and A.AQ$RC# = B.XA$RC# and A.AQ$PLU <> b.XA$PLU If there is no record selected (the keys match, but the $PLU values are the same), then a null is returned. If I remember correctly, the value(XA$PLU,0) will return a 0 if the field is null. But in this case, there is no record returned at all, so the value() function is not even used because there is no XA$PLU to even check for nulls. You have two choices I can think of: 1) Remove the "A.AQ$PLU <> b.XA$PLU" so it updates every record (this will still blow up if there is a record in kate that the keys don't exist in jbbidata) 2) Add criteria to the primary "where" clause to make sure the only records considered for update are the ones that don't match. Not knowing exactly your file design, what I come up with is this: update kate/f56525 A set AQ$PLU = (select Value(XA$PLU,0) from jbbidata/f57600 B where A.AQ$RCV = B.XA$RCV and A.AQ$RC# = B.XA$RC# and A.AQ$PLU <> b.XA$PLU) where AQ$DKY = 3 and Aq$DKM > 8 and A.AQ$RCV not in (select C.XA$RCV from jbbidata/f57600 C where A.AQ$RCV = C.XA$RCV and A.AQ$RC# = C.XA$RC# and A.AQ$PLU <> C.XA$PLU) Notes: 1) You'll probably find the value( ,0) is not not needed in this method unless XA$PLU sometimes contains nulls. 2) The "C" designation is not necessary, but I included it for clarification. 3) Definately not tested, but just written on the fly. Hopefully I got it right. 4) If anyone has a way around the redundant code in the two where clauses, I'd LOVE to see it. So far no one has known a way around it in SQL/400. MS Query can do it, though.
No announcement yet.
Null Value errorPage Title Module
Move Remove Collapse