Re: SQL question:

Tom,

You are not checking what happens if your SELECT conditions are not met.,
ie, if the result of your operation is NULL. Try this:

Update drvrmstrpf a
Set a.status=
COALESCE(
(select 'C' from locnmstrpf b
Where a.locn = b.locn and b.prdend=20100211)
, DEFAULT VALUE IF RECORD NOT FOUND
)

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Thu, Feb 18, 2010 at 11:15 AM, Tom <tomh@xxxxxxxxxxx> wrote:

I have 2 tables: DRVRMSTRPF, LOCNMSTRPF.



DRVRMSTRPF fields: LOCN, DRVR, STATUS

LOCNMSTRPF fields: LOCN, PRDEND, STATUS



All drvrmstrpf.locn values are found in locnmstrpf, but there may be some
locnmstrpf entries for which there are no corresponding entries in
drvrmstrpf.



I have a request from a user: update the drvrmstrpf status code to 'C'
for all locnmstrpf records with PRDEND value of 20100211.



I've tried the following SQL, but getting an error ("Null values not
allowed in column or variable STATUS"):



Update drvrmstrpf a

Set a.status=

(select 'C' from locnmstrpf b

Where a.locn = b.locn and b.prdend=20100211)



What am I missing?







--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.