CODEWITHSUNDEEP
sql
Animesh Porwal
Animesh Porwal

Reputation: 567

In SQL need to extract values from 2 columns containing NULL values

I am extracting records from a table and the condition is

(status_code is not in 'A') or (status_code is not in 'C' and SNbr is not in '01','02','03)

Both the columns can have duplicate values and can contain NULL values.

Here is the table data

status_code              SNbr
A                        01
A                        NULL
A                        04 
NULL                     NULL
NULL                     01
C                        NULL
C                        01
C                        04
B                        01
B                        NULL

Required O/P is 

status_code              SNbr
NULL                     NULL
NULL                     01
C                        NULL
C                        04
B                        01
B                        NULL

Thanks in advance for your help

Upvotes: 0

Views: 988

Answers (3)

Fred Sobotka
Fred Sobotka

Reputation: 5332

The wording of the condition in the question is misleading, since the two separate clauses cannot be connected by an OR and still produce the desired output. The sample output shown in the question demonstrates that all of the rules need to be enforced, not just one or the other. Here's how that looks as a WHERE clause in SQL:

SELECT * FROM someTable
WHERE COALESCE( status_code, '' ) != 'A' 
AND
( COALESCE ( status_code, '' ) != 'C' 
  OR COALESCE( SNbr, '' ) NOT IN ( '01', '02', '03' )
)
;

Upvotes: 0

rs.
rs.

Reputation: 27427

[Updated]Try this:

DECLARE @table TABLE (status_code varchar(4), snbr varchar(5))
INSERT INTO @table
SELECT 'A','01'
UNION SELECT 'A',NULL
UNION SELECT 'A','04' 
UNION SELECT NULL,NULL
UNION SELECT NULL,'01'
UNION SELECT 'C',NULL
UNION SELECT 'C','01'
UNION SELECT 'C','04'
UNION SELECT 'B','01'
UNION SELECT 'B',NULL


select * from @table where 
(ISNULL(status_code,'') IN ('C') AND ISNULL(SNbr,'') NOT IN ('01','02','03')) OR
(ISNULL(status_code,'') NOT IN ('C') AND ISNULL(status_code,'') != 'A')

Upvotes: 0

Shaikh Farooque
Shaikh Farooque

Reputation: 2640

select * from <tablename> where 
status_code != 'A' or status_code is null or SNbr is null or
(status_code !='C' and SNbr not in ('01','02','03'))

Upvotes: 1

Related Questions