CODEWITHSUNDEEP
sqlsql-servernull
XCCH004
XCCH004

Reputation: 323

NULLS being excluded when filtering string literal

I have the below query where I am intending to filter/exclude WHERE either the range type or the market concept equals the string literal 'PLACEHOLDER'. We have found due to feedback that we are actually also excluding any instance where those fields are NULL. This was some surprise to me, any advice on how to explain?

Query:

SELECT DISTINCT M.MATERIAL, 
       A.MARKET_CONCEPT, 
       A.RANGE_TYPE
FROM VW_MRP_ALLOCATION_COMBINED M
JOIN VW_ARTICLE_ATTRIBUTES_COMBINED A ON M.Material = A.Article AND M.SALES_ORGANIZATION = A.SALES_ORGANIZATION
WHERE M.stock_type = '' 
AND A.market_concept <> 'PLACEHOLDER'
AND A.RANGE_TYPE <> 'PLACEHOLDER' 
AND A.Article in ('BK0348',
'BQ2718',
'BQ2719',
'BS3674',
'CF3607',
'CF3608',
'CF3609',
'CF3610',
'CV5091',
'D94751',
'DH6911',
'DT5039')
AND M.Sales_Organization = 6040;

Upvotes: 0

Views: 711

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

I would suggest being explicit:

(A.market_concept <> 'PLACEHOLDER' OR A.market_concept IS NULL) AND
(A.RANGE_TYPE <> 'PLACEHOLDER' A.range_type IS NULL) AND

Note: This assumes that 'PLACEHOLDER' is not NULL. If that is possible, I would suggest asking a new question, with clear sample data and desired results.

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 12969

You can use ISNULL function to consider NULL value same as placeholder.

SELECT DISTINCT M.MATERIAL, 
       A.MARKET_CONCEPT, 
       A.RANGE_TYPE
FROM VW_MRP_ALLOCATION_COMBINED M
JOIN VW_ARTICLE_ATTRIBUTES_COMBINED A ON M.Material = A.Article AND M.SALES_ORGANIZATION = A.SALES_ORGANIZATION
WHERE M.stock_type = '' 
AND ISNULL(A.market_concept,'PLACEHOLDER') <> 'PLACEHOLDER'
AND ISNULL(A.RANGE_TYPE,'PLACEHOLDER') <> 'PLACEHOLDER' 
AND A.Article in ('BK0348',
'BQ2718',
'BQ2719',
'BS3674',
'CF3607',
'CF3608',
'CF3609',
'CF3610',
'CV5091',
'D94751',
'DH6911',
'DT5039')
AND M.Sales_Organization = 6040;

Upvotes: 1

George Joseph
George Joseph

Reputation: 5922

NULL records are drawn using the "IS NULL" keyword for comparison. Here is an example how you can get null records

with data
  as (select 'PLACEHOLDER' as market_concept,'PLACEHOLDER' as range_type
      union all
      select 'MarketConcept1' as market_concept,'Rangetype1' as range_type
      union all
      select null as market_concept, null as range_type
      )
 select *
 from data
 where ((market_concept <> 'PLACEHOLDER'
         and range_type <>'PLACEHOLDER'
         )
      --This OR condition brings out the records which are null
      OR(market_concept is null
         and range_type is null
         )
       )

+----------------+------------+
| market_concept | range_type |
+----------------+------------+
| MarketConcept1 | Rangetype1 |
| null           | null       |
+----------------+------------+

Upvotes: 2

Related Questions