Need a fresh set of Eyes and an opion from an expert!!!

• stax68

  SSChampion

  Points: 11761

  More actions

  July 9, 2006 at 5:58 am

  #648120

  Your problem most probably arises from the presence of NULLs in your employees table:

  Q1:

  --original 'valid reps' query - correct results

  select

  i ValidReps_Original_Query from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  where

  i in (1,2,4,null) --ValidReps

  Q2:

  --original 'invalid reps' query, attempt 1 - fails "ActualReps.i <> null" on every row (see expanded version: Q6).

  select

  i InvalidReps_Original_Query_1

  from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  where

  i not in (1,2,4,null) --ValidReps

  Q3:

  --original 'invalid reps' query, attempt 2 - fails "null <> ValidReps.i" at row 6 of ActualReps.

  select

  i InvalidReps_Original_Query_2

  from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  where

  i not in

  (

  select

  * from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  where

  i in (1,2,4,null) --ValidReps

  )

  --Q1

  Q4:

  --NOTE: both inequality and equality operators evaluate to 'unknown', i.e. not 'true', if either operand is null.

  declare

  @i int

  select @i = null

  select

  case when 1 = @i then 'TRUE'

  when not(1 = @i) then 'FALSE'

  else 'UNKNOWN' end NullComparison

  --

  --Expanded versions

  Q5:

  -- Q1 is equivalent to:

  select

  i ValidReps_Original_Query_Expanded from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  where

  i = 1 or i = 2 or i = 4 /* or i = null */ --last disjunct is never true, so irrelevant

  Q6:

  -- Q2 is equivalent to:

  select

  i InvalidReps_Original_Query_1_Expanded from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  where

  i <> 1 and i <> 2 and i <> 4 and i <> null --last conjunct never true, so whole expression never true.

  --

  --to avoid NULL problems, use the equivalents of:

  Q7:

  --show both valid and invalid counts (inc nulls)

  select

  sign(isnull(ValidReps.i,0)) IsValid, count(*) ActualReps

  from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  left

  join (select 1 i union select 2 union select 4 union select null) ValidReps --DISTINCT list

  on

  ActualReps.i = ValidReps.i

  group

  by sign(isnull(ValidReps.i,0))

  Q8:

  --show valid count - equivalent to Q1, Q5

  select

  1 IsValid, count(*) ActualReps

  from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  join

  (select 1 i union select 2 union select 4 union select null) ValidReps --DISTINCT list

  on

  ActualReps.i = ValidReps.i

  Q9:

  --show invalid count (inc nulls) - equivalent to Q7

  select

  0 IsValid, count(*) ActualReps

  from

  (

  select 1 i union all select 2 union all select 3 union all select 4 union all select 5 union all select null) ActualReps

  left

  join (select 1 i union select 2 union select 4 union select null) ValidReps --DISTINCT list

  on

  ActualReps.i = ValidReps.i

  where

  ValidReps.i is null

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• J Gross

  Valued Member

  Points: 61

  More actions

  August 30, 2006 at 9:11 am

  #657564

  This is a late update but the problem was with nulls.  Thank you.