SAME Query Returns different results

Posted on 2011-09-29
Last Modified: 2012-05-12
I have the query below, and it has prompted the question today.

select *
from DB..TableA  
where (Discontinued = 0 or Discontinued = null)
and (Accessory = 0 or Accessory is null)
and (ltrim(Rtrim(PartNumber)) not in (Select distinct ltrim(Rtrim(PartNumber)) from DB..TableB))
and (ltrim(Rtrim(PartNumber)) not in (select Distinct ltrim(Rtrim(PartNumber)) from DB..TableC))

The first 5 times I ran the query zero records were returned, which I know was incorrect, on the 6th time running the query, WITHOUT modifying it or any data with in the databases, I received over 600 results which is more along the lines of what I am expecting.

I have seen this once before but marked it up as a PC issue as I was having a lot of issues with the PC that day.  

What would cause this behavior when the query is not returning what it should and then with out any changes it starts returning the correct records?

Question by:cpence
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Accepted Solution

lludden earned 125 total points
ID: 36817647
One problem:
where (Discontinued = 0 or Discontinued = null) is a problem.  It should be IS NULL

It might have timed out - having a calculation in your WHERE clause like that forces a table scan (ltrim(Rtrim(PartNumber)) isn't indexed, so it will scan every row in the table.


Expert Comment

ID: 36818807
If the query does take a while to run, then I would agree with lludden above. The query is timing out. You could be having communication issues with the database from the tool you are using or just from your PC. Do you have any problems running the query on anyone else's PC?

If the issue is the query timing out, then you may need to alter the query's where clause. Depending on the size of the tables in my Sub queries, I often use the EXISTS statement.

select *
from DB..TableA A
where (Discontinued = 0 or Discontinued = null)
and (Accessory = 0 or Accessory is null)
and not exists(select * from DB..TableB B where ltrim(Rtrim(B.PartNumber)) = ltrim(Rtrim(A.PartNumber)) 
and not exists(select * from DB..TableC C where ltrim(Rtrim(B.PartNumber)) = ltrim(Rtrim(A.PartNumber))

Open in new window


Author Comment

ID: 36893393
it is occuring on multiple PCs running SSMS
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 total points
ID: 36896953
Please review lludden's comment. NULLs are filtered automatically if you do a direct comparison on a field like (Discontinued = 0); however, to fix this one can NOT do direct comparisons to NULL. It has to be (Discontinued IS NULL) as pointed out by lludden. The reason the query fails is (Discontinued = NULL) will always be a false statement and so no results meet the criteria. If this works on occasion, either that scenario does not involve rows with NULL in Discontinued field OR you are getting lucky that the query picks up the (Discontinued = 0) and skips processing of the =NULL bit. This is not guaranteed and erratic as you have seen and should be avoided.

You query should be:
select * /* replace with list of columns you want. */
from DB..TableA  
where (Discontinued = 0 or Discontinued is null)
and (Accessory = 0 or Accessory is null)
and (ltrim(Rtrim(PartNumber)) not in (Select distinct ltrim(Rtrim(PartNumber)) from DB..TableB))
and (ltrim(Rtrim(PartNumber)) not in (select Distinct ltrim(Rtrim(PartNumber)) from DB..TableC))

Also as pointed out, the highlighted statement might be causing some inefficiency. Try fixing the original data if you can to eliminate the need to LTRIM() and RTRIM() each time you reference that field.   Good luck!

Author Closing Comment

ID: 36905199
Thanks guys,
LVL 60

Expert Comment

by:Kevin Cross
ID: 36905220
Why the "B"? What else is missing from our explanation if you do not mind my asking?
Not critical, I am just curious. Anyway, glad we could help.

Best regards and happy coding,


Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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