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