Re: SQL question:

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.