× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.