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?

`WHERE NOT` ? Or, `<>` ? It depends. And it's not clear exactly what you're attempting to exclude. — devlin carnate, Sep 24 '20 at 15:06
you are using decode on both sides...this not what I proposed! — PKey, Sep 25 '20 at 11:58

score 1 · Accepted Answer · answered Sep 24 '20 at 15:19

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

answered Sep 24 '20 at 15:19

PKey

3,715
1
14
39

did you see my EDIT? – DerBenniAusA Sep 25 '20 at 11:44
@DerBenniAusA saw it just now..what do you mean by 'valid'? In proposed solution 00 is converted to 'xNVLx' , 01 is also converted. to 'xNVLx' rest values remain the same... – PKey Sep 25 '20 at 11:50
@DerBenniAusA this is done on the right. side only. – PKey Sep 25 '20 at 12:03
I solved it this way now: and not ( nvl(t.code, 'xNVLx') = nvl(t2.code, 'xNVLx') or t.code is null and t2.code = '00' or t.code is null and t2.code = '01' ) – DerBenniAusA Sep 25 '20 at 12:58

score 0 · Answer 2 · answered Sep 24 '20 at 15:10

0

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

answered Sep 24 '20 at 15:10

Gordon Linoff

1,242,037
58
646
786

@DerBenniAusA . . I can't parse your comment in a reasonable way. This answers the question. – Gordon Linoff Sep 25 '20 at 12:46

How to exclude records from SQL query?

2 Answers2