WHERE clause not filtering correctly

Posted on 2012-09-07
Medium Priority
Last Modified: 2012-09-07
I have a query which tries to calculate single mothers into groups.  It seems to work except for the last part of the WHERE statement which says "activity.NumChildren != '0' AND activity.NumChildren IS NOT NULL"  I've tried several different ways to accomplish but to no avail so far.  It always returns the same set of numbers.  The NumChildren field is varchar column.  The entire query is listed below.

Thanks for any help on figuring this out.

@username varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'



COUNT(CASE WHEN maritalstatus = 'S'
THEN 1 ELSE 0 END) AS 'Single (S)',
COUNT(CASE WHEN maritalstatus = 'W'
THEN 1 ELSE 0 END) AS 'Widowed (W)',
COUNT(CASE WHEN maritalstatus = 'E'
THEN 1 ELSE 0 END) AS 'Separated (E)',
COUNT(CASE WHEN maritalstatus = 'D'
THEN 1 ELSE 0 END) AS 'Divorced (D)',
THEN 1 ELSE 0 END) AS 'Missing'

FROM client 
WHERE client.CharityCkID IN 
(SELECT activity.CharityCkID

FROM activity inner join client on client.CharityCkID=activity.CharityCkID

WHERE activity.username = @username AND activity.date >= @dStart AND activity.date <= @dEnd AND activity.NumChildren != '0' AND activity.NumChildren IS NOT NULL

Open in new window

Question by:pposton
LVL 25

Expert Comment

by:Lee Savidge
ID: 38376568
Assuming activity.NumChildren is a varchar or nvarchar:

where isnull(activity.NumChildren, '0') != '0'

If it is numeric:

where isnull(activity.NumChildren, 0) != 0
LVL 66

Expert Comment

by:Jim Horn
ID: 38376575
Two things:

(1)  Try WHERE COALESCE(activity.NumChildren, 0) <> 0
(2)  <probably not the solution, but anways...>  Looking at your WHERE clause you have a lot of AND's, without parenthesis, which means that every expression must eveluate to TRUE for a row to be returned.   You may want to consider parentheses to set order of precedence.
LVL 25

Expert Comment

by:Lee Savidge
ID: 38376581
where isnull(activity.NumChildren, '0') <> '0'


where isnull(activity.NumChildren, 0) <> 0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

LVL 25

Expert Comment

ID: 38376589
Should your COUNT(CASE ...) be SUM(CASE ...)?
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 38376892
Since NumChildren is varchar, you could have ANYTHING in that column.

The only really safe way to do a numeric comparison on that column is a CASE statement; for example:

    activity.username = @username AND
    activity.date >= @dStart AND
    activity.date <= @dEnd AND
    1 = CASE
        WHEN NumChildren IS NULL THEN 0
        WHEN NumChildren LIKE '%[^0-9]%' THEN 0 --if any nonnumeric char, exclude
        WHEN NumChildren > 0 THEN 1 --safe, since only digits appear in value
        ELSE 0 END

Author Closing Comment

ID: 38377321
Thanks!  Great solution as it takes into consideration there might be non numeric characters in the field.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question