Solved

SQL Null not returning results

Posted on 2008-06-10
6
193 Views
Last Modified: 2010-03-20
Ok I am writing a very simple statement but I must be missing something...

select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0

The above statement returns all the values I expect (results are posted below in the CODE SNIPPET).

However, when I run the same query but wanting to show only the results where tsp_srvc_equipmentididname is null

select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0 and tsp_srvc_equipmentididname='null'

I get NOTHING returned.


CAROLINA LAKES GOLF CLUB, LLC	062704.JTE

CAROLINA LAKES GOLF CLUB, LLC	NULL

FIRETHORNE GC	NULL

WILSON COUNTRY CLUB	NULL

THE GOLF CLUB AT STAR FORT, INC.	NULL

GAFFNEY CC	NULL

Open in new window

0
Comment
Question by:r270ba
  • 3
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
NIMTUG_Simon earned 500 total points
ID: 21754039
Try tsp_srvc_equipmentididname is NULL

(No quotes around the NULL and replace = with IS)
0
 
LVL 3

Expert Comment

by:NIMTUG_Simon
ID: 21754052

select accountidname, tsp_srvc_equipmentididname from incident

where subjectidname='turf equipment' and statecode=0 and tsp_srvc_equipmentididname IS NULL

Open in new window

0
 

Author Comment

by:r270ba
ID: 21754063
Perfect...I knew I was missing something easy...I was doing ='null' and isnull and = null but I did not know to put a space between is and null :).

Thanks!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21754064
NULL is special:
select accountidname, tsp_srvc_equipmentididname from incident
where subjectidname='turf equipment' and statecode=0 and tsp_srvc_equipmentididname IS NULL

Open in new window

0
 
LVL 3

Expert Comment

by:NIMTUG_Simon
ID: 21757175
ISNULL is actually a function which you can use to replace the value with a set value is the original value is null.

So if you have a table (t1) with

col1
=====
'A'
'B'
NULL
'C'

you can have a query which goes

Select ISNULL(col1, 'No Value') as col1 FROM t1

You will get

'A'
'B'
'No Value'
'C'
0
 

Author Comment

by:r270ba
ID: 21760068
sweet...thanks a lot guys!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

18 Experts available now in Live!

Get 1:1 Help Now