Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

code help where clause Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 3:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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'))
Post #1477284
Posted Wednesday, July 24, 2013 3:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 3,535, Visits: 7,618
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'))




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477290
Posted Wednesday, July 24, 2013 3:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 13,295, Visits: 12,145
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1477292
Posted Wednesday, July 24, 2013 3:31 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
Row with abc shouldn't show up since abc is the exclusion list. On yours it is appearing twice. In row 1 and 2.
Post #1477293
Posted Wednesday, July 24, 2013 3:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 3,535, Visits: 7,618
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1477299
Posted Wednesday, July 24, 2013 3:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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.
Post #1477302
Posted Thursday, July 25, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 13,295, Visits: 12,145
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.



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1477476
Posted Thursday, July 25, 2013 7:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 13,295, Visits: 12,145
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1477477
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse