Excluding records

• glenn.jack

  Grasshopper

  Points: 13

  More actions

  August 31, 2015 at 4:10 pm

  #302692

  Using MSSQL 2012

  I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.

  Below is my where clause.

  WHERE [DETERMINATION] <> 'Denied'

  AND [Number] ='A150731000039'

  ---- Removes incorrect records where these names match----

  AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

  Thanks,

  GJ

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  August 31, 2015 at 4:42 pm

  #1823513

  Glenn,

  any chance you could provide a create table script and some dummy records? Are all the records in the same table?

  thanks,

  Pieter

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  September 1, 2015 at 2:10 am

  #1823555

  Hi,

  if you want to include all rows except those mentioned in the last line, and want to retain rows with NULL values in the resultset, you will need something like this:

  AND (ISNULL([Name],'XXX') NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND ISNULL([PName],'DEFAULT') = 'DEFAULT')

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  September 1, 2015 at 2:23 am

  #1823556

  Just re-read your post and I think you have one more problem in your query.

  In words you have stated "to filter out any records where it finds those 3 Names that also have "Default" as the PName", but that is not what you have written in the code. What you have written in code means, that ALL rows that have anything else than DEFAULT in PName will be filtered out - not only those that match the NOT IN ('GLASSMAN','NANCY','LUDEMANN') condition.

  So, if I understand correctly, you would probably need this:

  AND (ISNULL([Name],'XXX') NOT IN ('GLASSMAN','NANCY','LUDEMANN') OR ISNULL([PName],'XXX') <> 'DEFAULT')

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 1, 2015 at 7:31 am

  #1823622

  WHERE [DETERMINATION] <> 'Denied'

  AND [Number] ='A150731000039'

  ---- Removes incorrect records where these names match----

  AND (

  [Name] IS NULL

  OR [PName] IS NULL

  OR ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

  )

  Avoid using ISNULL() in the WHERE clause; Google "SARGable".

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden