?
Solved

SQL Null not returning results

Posted on 2008-06-10
6
Medium Priority
?
201 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
[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
  • 3
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
NIMTUG_Simon earned 2000 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 143

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

800 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