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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.