CODEWITHSUNDEEP
sqlsql-server
masfenix
masfenix

Reputation: 7986

Using CASE WHEN in the where condition

I was wondering if anyone can help me write some code for the following logic.

We have a table 

----------------
id, lang, letter
----------------
1    1      E
1    1      E
1    1      E
1    1      E
2    2      F

Problem:

I need to select ALL the rows for which the following condition fails:

I know I can hard code it. Also i would like to do this in ONE query only.

Please help

Upvotes: 1

Views: 153

Answers (4)

onedaywhen
onedaywhen

Reputation: 57023

The idea here is that there are three business rules that may be implemented as three distinct tuple constraints (i.e. not false for every row in the table):

  1. id and lang must be equal (begging the question, why not make one a computed column?).

  2. If letter is 'E' then lang must be 1 (I assume there is a typo in your question where you said 'e' instead of 'E').

  3. If letter is 'F' then lang must be 2 (I assume there is a typo in your question where you said 2 instead of 'F').

The constraints 'don't have anything to say' about any other data (e.g. when letter is 'X') and will allow this to pass.

All three tuple constraints can be written in conjunctive normal form as a constraint validation query:

SELECT * FROM T
 WHERE id = lang
       AND ( letter <> 'E' OR lang = 1 ) 
       AND ( letter <> 'F' OR lang = 2 )

The data that violates the constraints can be simply shown (in pseudo relational algebra) as:

T MINUS (constraint validation query)

In SQL:

SELECT * FROM T
EXCEPT
SELECT * FROM T
 WHERE id = lang
       AND ( letter <> 'E' OR lang = 1 ) 
       AND ( letter <> 'F' OR lang = 2 )

It is good to be able to rewrite predicates in case one's query of choice runs like glue on one's DBMS of choice! The above may be rewritten as e.g. 

SELECT * FROM T
 WHERE NOT ( id = lang
            AND ( letter <> 'E' OR lang = 1 ) 
            AND ( letter <> 'F' OR lang = 2 ) )

Applying rewrite laws (De Morgan's and double-negative) e.g. 

SELECT * FROM T
 WHERE id <> lang
       OR ( letter = 'E' AND lang <> 1 ) 
       OR ( letter = 'F' AND lang <> 2 )

Logically speaking, this should be better for the optimizer because for the above to be a contradiction every disjunct member must be false (put another way, it only takes one OR'ed clause to be true for the data to be deemed 'bad'). In practice (in theory?), the optimizer should be able to perform such rewrites anyhow!

p.s. nulls are bad for logic -- avoid them!

Here's my test code with sample data:

WITH Nums AS ( SELECT * 
                 FROM ( VALUES (0), (1), (2) ) AS T (c) ), 
     Chars AS ( SELECT * 
                  FROM ( VALUES ('E'), ('F'), ('X') ) AS T (c) ), 
     T AS ( SELECT N1.c AS id, N2.c AS lang, 
                   C1.c AS letter
              FROM Nums AS N1, Nums AS N2, Chars AS C1 )

SELECT * FROM T
EXCEPT
SELECT * FROM T
 WHERE id = lang
       AND ( letter <> 'E' OR lang = 1 ) 
       AND ( letter <> 'F' OR lang = 2 );

Upvotes: -1

Taryn
Taryn

Reputation: 247670

I think this is what you want to exclude the records meeting that criteria:

create table #t
(
    id int,
    lang int,
    letter varchar(1)
)

insert into #t values (1, 1, 'E')
insert into #t values (1, 1, 'E')
insert into #t values (1, 1, 'E')
insert into #t values (1, 1, 'E')
insert into #t values (2, 2, 'F')
insert into #t values (1, 1, 'G')
insert into #t values (1, 1, 'H')
insert into #t values (1, 1, 'I')
insert into #t values (1, 1, 'J')
insert into #t values (2, 2, '2')


SELECT *
FROM #t
WHERE NOT
(
    id = lang
    AND
    (
        (
            lang = 1
            AND letter = 'E'
        )
        OR
        (
            lang = 2
            AND letter = '2'
        )
    )
)

drop table #t

to get the records with that, just remove the NOT it:

SELECT *
FROM #t
WHERE 
(
    id = lang
    AND
    (
        (
            lang = 1
            AND letter = 'E'
        )
        OR
        (
            lang = 2
            AND letter = '2'
        )
    )
)

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17957

WHERE NOT
(
    id = lang
    AND
    (
        (lang = 1 AND letter = 'e')
        OR (lang = 2 AND letter = '2')
    )
)

Upvotes: 3

Kevin DiTraglia
Kevin DiTraglia

Reputation: 26058

select * from table
where id <> lang and
(lang<>1 and letter <> 'e' or
lang<>2 and letter <> '2')

assuming you mean you want all data where both of those conditions are false.

Upvotes: 1

Related Questions