How to exclude records from SQL query?

Question

Given the following SQL query:

select t 
from tableA t, tableA t2 
where t.validAt = :validAt1 and t2.validAt = :validAt2 
and t.uniqueId = t2.uniqueId 
and nvl(t.code, 'xNVLx') != nvl(t2.code, 'xNVLx');

the result is that I get the records of the records with a changed value of column CODE. So far so good.

Examples:

CHANGES:

CODE changed from  123  -> 456:  IS IN RESULT SET, PERFECT
CODE changed from  123  -> NULL: IS IN RESULT SET, PERFECT
CODE changed from  NULL -> 123:  IS IN RESULT SET, PERFECT

NO CHANGES:

CODE changed from  NULL -> NULL: NOT IN RESULT SET, PERFECT
CODE changed from  123  -> 123:  NOT IN RESULT SET, PERFECT

Now there are two additional special cases that are to be added:

SPECIAL CASES:

Special Case 1: CODE changed from  NULL -> 00: SHALL NOT BE RESULT SET
Special Case 2: CODE changed from  NULL -> 01: SHALL NOT BE RESULT SET

Question: is there an elegant an simple SQL exclude existing?

EDIT:

I used the solution like proposed from @Plirkee:

and nvl(        decode(t.code,
                  '00','xNVLx',
                  '01','xNVLx',
                  t.code),
                'xNVLx')
                != nvl(
                decode(t2.code,
                  '00','xNVLx',
                  '01','xNVLx',
                  t2.code),
                'xNVLx')

but: with this logic change from "00" to "01" is treated as valid which should not.

Any ideas?

Answer 1

you could use decode function

select t 
from tableA t, tableA t2 
where t.validAt = :validAt1 and t2.validAt = :validAt2 
and t.uniqueId = t2.uniqueId 
and nvl(t.code, 'xNVLx') != nvl(decode(t2.code,'00','xNVLx','01','xNVLx',t2.code), 'xNVLx');

Answer 2

You can implement your logic as:

select . . .
from tableA t join
     tableA t2 
     on t.validAt = :validAt1 and t2.validAt = :validAt2 and
        t.uniqueId = t2.uniqueId 
where t.code <> t2.code or (t.code is not null and t.code is null);