Re: Newbie SQL problem with UPDATE - SQL0811 - repost

Bob,

Create/fill your LRACCTWF file with your students only query, then create an
INSERT statement that selects the teachers and all information they need,
with a default number for PIN that is blank or zero. Something like;

insert into LRACCTWF (field01, field02, field03)
(select teacher.info, field2, 0
from your.files
where your condition)

Jim

On Fri, Apr 10, 2009 at 2:24 PM, Bob Schwartz <rschwartz@xxxxxxxxxxxxxxx>wrote:

Charles,

I ran your SQL as suggested ...

From Charles:

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

================================================================================================

And zero (0) rows were returned.

I have 3 files
LRACCT lunch room POS acct file by PIN (5 digits)
LRIXEXTWF lunch room file with school info number/name
LRACCTWF lunch room POS acct file with school number

The LRACCT file has students and teachers in it.
I need to create LRACCTWF with teachers and students and update the school
number for students only.
I'm getting the school number from LRICEXTWF and joining on PIN -
LRPIN (5,0) from LRACCT, STUDE0001 (15A) from LTICEXTWF (substr 1,5).


Another way I may do it is like this ...

select a.*,
b.NUMBER
from LRACCT a,
LRICEXTWF b
where digits(a.LRPIN) = substr(b.STUDE00001,1,5)

This gets me all of the students with matching PIN numbers in LRACCT and
LRICEXTWF (#12,000+ records out of 18,000).
Then I would insert the remaining 6000 teacher records from LRACCT into
LRACCTWF, but I'm not sure how to do that??

Any ideas on this approach?

Many thanks to all! Bob


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