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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 59

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 59

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now