RE: Using SQL to check for duplicate records

[qsrvbas@xxxxxxxxxxxx (Tom Liotta)]

Booth:

The use of the word "key" was unfortunate. It was meant to indicate the 
specific positions of the records to check, not necessarily a defined DB2 key. 
The requirement for SQL, RPG, or probably any other similar useful technique, 
must be that the file is ordered on the "key"; i.e., the record positions used 
for ordering the file must be the same positions used for count(*) or lookahead 
and L1 or... whatever. Rather than "key", perhaps a phrase such as "sequence 
field" would be better.

There's no necessary relationship between DB2 file keys and lookahead other 
than the potential for messing up the duplicates test by presenting records in 
the wrong order. For SQL, a GROUP BY makes the order guaranteed; and GROUP BY 
is required for count(*) to work for duplicate testing. Lookahead does not 
provide ordering as GROUP BY does.

To tell the truth, the only reason I mentioned lookahead in the first place was 
because of the recent references to the cycle and 'obsolete' features in 
RPG400-L. I wasn't even really serious about it. But every rare once in a 
while, you see posts like "Gee, I never knew that existed and I needed it just 
this week!"

Tom Liotta

midrange-l-request@xxxxxxxxxxxx wrote:

>   2. RE: Using SQL to check for duplicate records (Booth Martin)
>
> The part I missed mostly is that there's no assurance that the fields to be
>checked are key fields.  In that case the RPG solution won't be of any use.

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