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

Question

Post reply

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

J Gross

Valued Member

Points: 61
More actions
July 7, 2006 at 5:51 pm

#118772

I'm in the middle of trying to clean up a mess for the company i just started with. Here is the situation.
I have a means of determining "Allowable Report_Names"
I have a means of obtainig "Actual Report Names"
What I am trying to do is get the "Actual Report_Names which do not match Allowable Report_Names" so that I can update some needed reference columns that are "somehow" blank. I have been tinkering with the script below for analysis to give me a better picture of what is going on but the queries are producing inconsistent results, especially with the last two queries.....shouldn't they produce the same results?

/*////////////////////////////////////////////////////////////////////////////////////////////////////////*/
/* ALL Actual ReportNames */
----Returns: 1477 Rows
SELECT Report_Name
FROM SERVER.THEDATABASE.dbo.Report
WHERE UserId = 'UserId'
------------------------------------------------------------------------------------
/* All Allowable Report_Names*/
---Returns: 3280 Rows
SELECT employeenum AS reference,
CASE WHEN LNameSubmitted IS NOT NULL THEN (lnamesubmitted + ', ' + fnamesubmitted)
      WHEN CHARINDEX(' ',fname,0) = 0 THEN (lname + ', ' + fname)
      ELSE (lname + ', ' + SUBSTRING(fname,0,CHARINDEX(' ',fname,0)))
END AS Report_Name
FROM employees WHERE submitdate IS NOT NULL)
------------------------------------------------------------------------------------
/*////////////////////////////////////////////////////////////////////////////////////////////////////////*/
/* All Actual Report_Names IN Allowable Report_Names */
----Returns: 979 Rows
SELECT Reference, Report_Name
FROM SERVER.THEDATABASE.dbo.Report
WHERE UserId = 'UserId'
AND Report_Name IN
(SELECT CASE WHEN LNameSubmitted IS NOT NULL THEN (lnamesubmitted + ', ' + fnamesubmitted)
       WHEN CHARINDEX(' ',fname,0) = 0 THEN (lname + ', ' + fname)
       ELSE (lname + ', ' + SUBSTRING(fname,0,CHARINDEX(' ',fname,0)))
  END AS Report_Name
FROM employees) ORDER BY Report_Name
-----------------------------------------------------------------------------------
/* All Actual ReportNames "NOT IN" Allowable ReportNames */
----Returns: 0 Rows -- WHY DOES THIS RETURN 0????
SELECT Reference, Report_Name
FROM SERVER.THEDATABASE.dbo.Report
WHERE UserId = 'UserId'
AND Report_Name NOT IN
/* All Allowable ReportNames */
(SELECT CASE WHEN LNameSubmitted IS NOT NULL THEN (lnamesubmitted + ', ' + fnamesubmitted)
       WHEN CHARINDEX(' ',fname,0) = 0 THEN (lname + ', ' + fname)
       ELSE (lname + ', ' + SUBSTRING(fname,0,CHARINDEX(' ',fname,0)))
  END AS Report_Name
FROM employees)
------------------------------------------------------------------------------------
/* All Actual ReportNames "NOT IN" Actual ReportNames "IN" Allowed Report_Names */
----Returns: 498 Rows -- Why does this return rows when above does not????
SELECT Reference, Report_Name
FROM SERVER.THEDATABASE.dbo.Report
WHERE UserId = 'UserId'
AND Report_Name NOT IN
/* All Actual ReportNames "IN" Allowable ReportNames */
(SELECT Report_Name
FROM SERVER.THEDATABASE.dbo.Report
WHERE UserId = 'UserId'
AND Report_Name IN
  /* All Allowable ReportNames */
  (SELECT CASE WHEN LNameSubmitted IS NOT NULL THEN (lnamesubmitted + ', ' + fnamesubmitted)
        WHEN CHARINDEX(' ',fname,0) = 0 THEN (lname + ', ' + fname)
        ELSE (lname + ', ' + SUBSTRING(fname,0,CHARINDEX(' ',fname,0)))
   END AS Report_Name
   FROM employees))
------------------------------------------------------------------------------------

Thank you,
Jason

Viewing 3 posts - 1 through 2 (of 2 total)

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

stax68 SSChampion Points: 11711 More actions · Answer 1

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 · Answer 2

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