WHERE clause not filtering correctly

Posted on 2012-09-07
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 >= @dStart AND <= @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
    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 65

    Expert Comment

    by:Jim Horn
    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
    where isnull(activity.NumChildren, '0') <> '0'


    where isnull(activity.NumChildren, 0) <> 0
    LVL 25

    Expert Comment

    Should your COUNT(CASE ...) be SUM(CASE ...)?
    LVL 68

    Accepted Solution

    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 >= @dStart AND <= @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

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

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now