Solved

SQL Null not returning results

Posted on 2008-06-10
6
195 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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