Re: Newbie SQL problem with UPDATE - SQL0811 - repost

[Charles Wilt <charles.wilt@xxxxxxxxx>]

This means that you have something like so in LRICEXTWF
Number    Stude00001
1              ABCDE1234
2              ABCDE5678

So the DB doesn't know if it should update the field in LRACCTWF with
a 1 or a 2 for substr(b.stude00001,1,5) = 'ABCDE'


If you want to see the problem children:
SELECT  substr(b.stude00001,1,5), b.number, count(*)
FROM   LRICEXTWF b
group by substr(b.stude00001,1,5), b.number
having count(*) > 1

In order for your update to work, the above statement must not return any rows.

Charles


On Fri, Apr 10, 2009 at 2:52 PM, Bob Schwartz <rschwartz@xxxxxxxxxxxxxxx> wrote:
> FWIW I just tried this ...
>
>         ************************** Beginning of Data *********************************
>  0001.00 UPDATE LRACCTWF a
>  0002.00  SET a.NUMBER = (SELECT distinct b.NUMBER
>  0003.00                  FROM LRICEXTWF b
>  0004.00                  WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))
>  0005.00 WHERE EXISTS
>  0006.00                 (SELECT distinct b.number
>  0007.00                  FROM   LRICEXTWF b
>  0008.00                  WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))
>         ***************************** End of Data ************************************
>
> Still getting SQL0811 ???? Thank you, Bob
>
>
> From: Bob Schwartz
> Sent: Friday, April 10, 2009 2:17 PM
> To: 'midrange-l@xxxxxxxxxxxx'
> Subject: Newbie SQL problem with UPDATE - SQL0811 - repost
>
>
> I'm trying to update a file with values from another file using the QM SQL statement below.
>
> I keep getting SQL0811 - Result of select more than one row.
>
> Can someone help me out, I've been all over the place and can't figure this one out.
>
> Actually found some of this searching the archives - Thanks  Elvis B.
>
>
>
> Thank you, Bob
>
>
>
>        ************************** Beginning of Data *********************************
>
>  0001.00 UPDATE LRACCTWF a  SET a.number   =
>
>  0002.00   COALESCE((SELECT b.number
>
>  0003.00    FROM   LRICEXTWF b
>
>  0004.00    WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5)),number)
>
>  0005.00 WHERE EXISTS
>
>  0006.00  (SELECT b.number
>
>  0007.00   FROM   LRICEXTWF b
>
>  0008.00    WHERE DIGITS(a.lrpin) = substr(b.stude00001,1,5))
>
>         ***************************** End of Data ************************************
>
>
> Bob Schwartz
> Director of Technology Services
> Glynn County School System
> 1313 Egmont Street
> Brunswick, GA 31520
>
> 912-280-1045 Office
> 912-223-2924 Cell
> 912-265-2011 Fax
>
> www.glynn.k12.ga.us
> rschwartz@ glynn.k12.ga.us
> --
> 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.