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