Post reply

NOT IN affecting output

  • February 25, 2015 at 9:56 am

    #301604

    Hello

    I have a fairly simple script, but one field (person_name) has some test users' names (e.g. 'John Smith') in that I want to exclude from a report I am creating. The field also has some null values, which I want to keep in the report.

    I have put in:

    AND person_name NOT IN ('john smith', 'sally brown') etc.

    This removes the names from the report but, for some reason, it has also removed the NULL values from person_name. As mentioned, I want to keep the NULL values in.

    My question is, is there a simple reason why NULL has vanished from the report as soon as I start blocking certain names?

    I am rather puzzled by this and any pointers would be appreciated.

    Thanks.

  • February 25, 2015 at 10:06 am

    #1779982

    null cannot be compared to any value, as NULL is undefined,so it affected your logic.

    ...AND ISNULL(person_name,'') NOT IN ('john smith', 'sally brown') would resolve the issue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 25, 2015 at 10:29 am

    #1779993

    Thanks very much for getting back.

    I have tried as you suggested:

    AND ISNULL(person_name) NOT IN ('john smith', 'sally brown')

    But got the error message 'The isnull function requires 2 arguments'

    I think I may have a comma missing, or something - please can you advise?

    Many thanks.

  • February 25, 2015 at 10:39 am

    #1780002

    you ar emissing the empty string part i put in my example:

    ISNULL(person_name,'')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 25, 2015 at 3:17 pm

    #1780079

    Thanks - I misread the '' part. Worked perfectly.

    Many thanks for taking the time and trouble to help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply