July 24, 2013 at 3:09 pm
What is the correct way of coding this WHERE clause?
By putting or, I am getting some records with the exclusion values.
If replace or with AND in the second line, I won't get the exlcusion values but I won't get where a.col1/d.col2 is NULL. I want all the records where a.col1 or d.col2 is NULL and all other values except the ones specified in the exclusion list.
AND isnull(a.col1, ' ') NOT IN ('abc', 'def', 'ghi'))
or (d.col2 is null or isnull(d.col2,' ') NOT IN ('abc', 'def', 'ghi'))
July 24, 2013 at 3:22 pm
I don't get it. Could you please post sample data and expected results? Check the article linked on my signature to guide you.
Your description and the results seem to match, but I might be misunderstanding the whole problem.
This is what I used to test.
WITH CTE(col1, col2) AS(
SELECT 'abc' col1, 'bbc' col2 UNION ALL
SELECT 'nbc' col1, 'abc' col2 UNION ALL
SELECT NULL col1, NULL col2 )
SELECT *
FROM CTE
WHERE ISNULL( col1, '') NOT IN ('abc', 'def', 'ghi')
OR (col2 is null or isnull(col2,' ') NOT IN ('abc', 'def', 'ghi'))
July 24, 2013 at 3:31 pm
There really is no need for the ISNULL checks here. NULL is not in any of those value either. All the ISNULL check does here is makes it a nonSARGable predicate.
AND a.col1 NOT IN ('abc', 'def', 'ghi')
or (d.col2 is null or d.col2 NOT IN ('abc', 'def', 'ghi'))
I have to agree with Luis thought that without some clarification it is hard to know what you want here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2013 at 3:31 pm
Row with abc shouldn't show up since abc is the exclusion list. On yours it is appearing twice. In row 1 and 2.
July 24, 2013 at 3:39 pm
Sean Lange (7/24/2013)
There really is no need for the ISNULL checks here.
There's a need for a NULL validation since the OP wants to show NULLs as well.
July 24, 2013 at 3:46 pm
WITH CTE (col1, col2, col3) AS
(
SELECT '1' col1,'abc' col2, 'bbc' col3 UNION ALL
SELECT '2'col1,'nbc' col2, 'zhi' col3 UNION ALL
SELECT '3'col1, NULL col2, 'abc' col3 UNION ALL
SELECT '4'col1, 'gef' col2, NULL col3 UNION ALL
SELECT '5'col1, NULL col2, NULL col3
)
SELECT *
FROM CTE
On this one, it should only show up row 2, 4 and 5. I want to keep if col2 or col3 is null but they need to be exluded if they have abc, def or ghi in them.
July 25, 2013 at 7:21 am
Luis Cazares (7/24/2013)
Sean Lange (7/24/2013)
There really is no need for the ISNULL checks here.There's a need for a NULL validation since the OP wants to show NULLs as well.
You are right Luis. I misread that entirely. I really should learn to read the whole post before jumping to conclusions. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2013 at 7:24 am
SQL_Surfer (7/24/2013)
WITH CTE (col1, col2, col3) AS(
SELECT '1' col1,'abc' col2, 'bbc' col3 UNION ALL
SELECT '2'col1,'nbc' col2, 'zhi' col3 UNION ALL
SELECT '3'col1, NULL col2, 'abc' col3 UNION ALL
SELECT '4'col1, 'gef' col2, NULL col3 UNION ALL
SELECT '5'col1, NULL col2, NULL col3
)
SELECT *
FROM CTE
On this one, it should only show up row 2, 4 and 5. I want to keep if col2 or col3 is null but they need to be exluded if they have abc, def or ghi in them.
Like this?
SELECT *
FROM CTE
where (col2 is null or col2 not in ('abc', 'def', 'ghi'))
AND
(col3 is null or col3 not in ('abc', 'def', 'ghi'))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply