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' )
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