Sorry guys, but OS/400 is smart enough to use an index even with a NOT IN.

A test on v5r2....

SELECT SPLVL, count(*)
FROM cstspnp
group by splvl

Salesperson Level COUNT ( * )
Y 1
2 4
1 299,078
N 3


select * from cstspnp
where splvl not in ('1',' ')

Access path of file CMWTEST2 was used by query.
for reason code 1.
1 - Record selection.

I think the issue is that usually a NOT IN selects enough records that a table scan is the best


Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of DeLong, Eric
Sent: Wednesday, July 16, 2008 12:36 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Help finding missing records

I don't think so..... NOT EXISTS will use an index scan *IF* the fields
referenced in the WHERE of the subquery exist within an index. I would
agree with that statement in the case of the IN predicate....

Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Wednesday, July 16, 2008 10:32 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Help finding missing records

Caveat emptor!!

Remember that NOT EXISTS, as well as NOT IN, forces a table scan - you
can't tell if something is not there unless you look at everything.
Learned this at the last COMMON, I think.

Now probably and EXCEPTION JOIN can do better, IF you have supporting


This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page