Re: SQL question:

Alan,

With due respect, I think that in the case you make reference to, the error
message is quite explicit, something in the order of "SELECT returns more
than one result" (not exact wording).

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Thu, Feb 18, 2010 at 11:32 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi Tom
what I have found this to usually mean is that the field LOCN is not
unique, therefore the SQL cannot determine which of the records need to be
linked


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



"Tom"
<tomh@xxxxxxxxxxx
> To
Sent by: "'Midrange Systems Technical
midrange-l-bounce Discussion'"
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

02/18/2010 10:59 Subject
AM SQL question:


Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






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.


--
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.