Null Value error

[dacust]

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.

[kgregory@benbridge.com]

Daniel, I changed the selection as you sugested and it worked. Many, many thanks. Kate

[kgregory@benbridge.com]

Hi, I've never used the VALUE or COALESCE functions before. Can anyone tell me why this code doesn't work? 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 Null values not allowed in column or variable AQ$PLU. I'm trying to put XA$PLU from file B into AQ$PLU in file A when they don't match. Thanks, Kate

[J.Pluta]

Does this work? update kate/f56525 A set AQ$PLU = COALESCE((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), AQ$PLU) where AQ$DKY = 3 and Aq$DKM > 8 The idea is to use the original value if the SELECT returns a NULL. I don't know if the syntax works, and I'm about to walk out the door, but I'll give it a shot when I have time. Joe