Solved

Simple SQL Select question

Posted on 2007-04-09
2
218 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
2 Comments
 
LVL 3

Accepted Solution

by:
JipFromParis earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
whm high memory usage in processes 7 69
how to extract a number from a MS SQL server string. 39 70
Substring() and LEFT() syntax 4 21
Oracle query output question 4 36
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

19 Experts available now in Live!

Get 1:1 Help Now