I want to return only KTAN8 that do not have DEL, DELA, MKT. I keep getting all KTAN8 with DEL, DELA, or MKT along with other modules that the KTAN8 has.
SELECT
KTAN8,
KT$MPC,
KT$PMD
FROM
IRIS01.FTDR2PDTA.F55431
WHERE
EXISTS
( SELECT KTAN8 FROM IRIS01.FTDR2PDTA.F55431
WHERE KT$PMD <> 'DEL'
or KT$PMD <> 'DELA'
or KT$PMD <> 'MKT' )
Requires Free Membership to View
This is a nice example of the difficulties that can arise when mixing ORs with NOTs.
Suppose KT$PMD is equal to 'DEL'. Therefore it will fail the first test, KT$PMD<>'DEL'. Since 'DEL' is equal to 'DEL', therefore 'DEL'<>'DEL' is false.
However, it will pass the second test, KT$PMD<>'DELA'. Since 'DEL' is not equal to 'DELA', therefore 'DEL'<>'DELA' is true. And because you have combined the conditions with ORs, it turns out that the WHERE clause must evaluate to true for every single row, no matter what value KT$PMD has. If it's equal to one of those values, it's automatically not equal to the other two, thus the WHERE clause is true.
You don't really need a subquery for this. All you need to do is make sure that the value isn't equal to any of the given values.
There are several ways to do it:
select KTAN8
, KT$MPC
, KT$PMD
from IRIS01.FTDR2PDTA.F55431
where KT$PMD <> 'DEL'
and KT$PMD <> 'DELA'
and KT$PMD <> 'MKT'
select KTAN8
, KT$MPC
, KT$PMD
from IRIS01.FTDR2PDTA.F55431
where not
( KT$PMD = 'DEL'
or KT$PMD = 'DELA'
or KT$PMD = 'MKT' )
select KTAN8
, KT$MPC
, KT$PMD
from IRIS01.FTDR2PDTA.F55431
where not KT$PMD
in ( 'DEL','DELA','MKT' )
My preference is for the last one, because it's easier to understand. Any time you can simplify a complex expression which contains NOTs, ANDs, and ORs, it will be easier to understand and therefore easier to maintain if when changes are required.
This was first published in November 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation