Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Simple SQL Select question

Posted on 2007-04-09
2
Medium Priority
?
225 Views
Last Modified: 2013-12-24
I have a table that has a RecStatus field in it.  For certain record types, the RecStatus is empty.
When I run the sample queries below, only the records with a RecStatus <> Imported or Pursuing are returned. (ie. RecStatus=Maintaining or New are returned)  The records where RecStatus is empty are also not returned.  Can someone tell me why?  Can someone tell me how to fix?  I need the empty RecStatus records as well.

Sample 1:
<cfquery name="sqlAccounts" datasource="#Request.SomeDSN#">
SELECT * FROM tblAccts WHERE RecStatus <> 'Imported' AND RecStatus <> 'Pursuing'
</cfquery>
Sample 2:
<cfquery name="sqlAccounts" datasource="#Request.SomeDSN#">
SELECT * FROM tblAccts WHERE RecStatus NOT IN('Imported','Pursuing')
</cfquery>
0
Comment
Question by:sulzener
[X]
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
2 Comments
 
LVL 3

Accepted Solution

by:
JipFromParis earned 2000 total points
ID: 18878038
I assume the RecStatus column allow NULL values. NULL values never match any predicate in a WHERE clause, albeit the IS NULL predicate. So, you should rewrite the first request as :

SELECT * FROM tblAccts WHERE (RecStatus <> 'Imported' AND RecStatus <> 'Pursuing') OR (RecStatus IS NULL)
0
 

Author Comment

by:sulzener
ID: 18878550
Thanks ... Not sure that has ever registered with me before.  Thanks for clearing that up.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

609 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