RE: Using SQL to check for duplicate records

Booth:

The suggestion came out of trying to duplicate Rob's specific request where 
each printed line of output showed the key value and the RRN for a record. By 
using lookahead (which is still discussed in the RPG/400 Reference accessed via 
V5R2 InfoCenter), you can know if the first record in an L1-group is the only 
record in the group... that is, if lookahead works like I've thought it should.

In any other RPG technique, when you've read the first record, how do you know 
whether to print it or not? Well, you don't. You save the needed values and 
read the next record. After reading the next record, you decide whether to 
print the previous values or not. Then, you decide what to do with the current 
record. And when you get the first record of a new group, what do you do about 
the last record of the previous group?

Well, lookahead lets you take a sneak peek at the next record. No need to save 
any values, no need to complicate the logic. Just make a direct comparison for 
the first record of a group and the decision is made for every record in the 
group, whether the group contains one record or 50000.

Two F-specs, three I-specs, two lines of C-specs, two lines of O-specs (add 
headings for neatness). Together with the extraction of RRN, it's pretty 
direct. 

In SQL, just getting count(*) for the groups could take a chunk of processing. 
I'm not sure it could be any more efficient in any other language.

Tom Liotta

midrange-l-request@xxxxxxxxxxxx wrote:

>   8. RE: Using SQL to check for duplicate records (Booth Martin)
>
>I am not sure look ahead fields are still supported in RPGIV.  In any event,
>what would a look ahead field gain for you?  The cycle works just fine.
>Print a line anytime L1 is not on.  Its really that simple.  There's no need
>for anything fancier.
>
>-------Original Message-------
>
>Hmmm... _IF_ I were doing it in RPG, I might define the L1 field (L1FLD) and
>then define the same record positions as a lookahead field (LAFLD). At
>L1-detail time, I'd seton an indicator if L1FLD = LAFLD. The indicator would
>be setof at L1-total time. Output would be printed at detail time as long as
>the test indicator was on. It should be on for every record in the L1-group
>if there any duplicates and off if no duplicates.
>
>But I've never done it, so don't know if it'd work. I'd probably go with
>whatever SQL seemed to work well (to keep it from being a pure RPG post and
>needing to be sent to different list.)

-- 
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertech.com


__________________________________________________________________
New! Unlimited Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Act now to get a personalized email address!

Netscape. Just the Net You Need.