Re: Newbie SQL problem with UPDATE - SQL0811 - repost

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.