CFQUERY not capturing records with 'NULL' values

I have a query that is not capturing records with 'NULL' values...it captures records that has no value in the field but if the SQL table has the 'NULL' value in the field, it doesn't appear in my query.  For example:

ID     myID      Name
1     NULL      Jack
2                    Jim
3     NULL      Homer
4     IN578      Jill
5     89787     Harriett

So in my query (shown below) I want all records that doesn't have the myID that starts with IN.  So in essence, I should get all records except for #4.  Instead, I get #2 and #5.

Also, the funny thing is...if I don't have the NOT LIKE line in my query,, it then gives me all records.
<cfquery datasource="myDatasource" name="myQuery">
SELECT *
FROM    myTable
WHERE   myID NOT LIKE 'IN%'
</cfquery>

Open in new window

Lee R Liddick JrReporting AnalystAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
>  As soon as I add anything else to the statement whether it be the NOT LIKE 'IN%'
> or eliminating empty strings, whatever, it gives me nothing.

You are probably using the  _AND_ operator for the subsequent conditions.   That would not work because "myID" cannot be null _and_ something else at the same time.

--- does NOT work
WHERE   myID NOT LIKE 'IN%'    --- fails to pickup nulls. so nothing is returned
AND      myID IS NULL
0
 
erikTsomikConnect With a Mentor System Architect, CF programmer Commented:
can you do this
<cfquery datasource="myDatasource" name="myQuery">
SELECT *
FROM    myTable
WHERE   myID NOT LIKE 'IN%' or myID is null
</cfquery>
0
 
SidFishesConnect With a Mentor Commented:
"it captures records that has no value in the field but if the SQL table has the 'NULL' value in the field"

just to clarify

1     NULL      Jack
2                    Jim

null is not a value..it is nothing, nada, errr...null in the case of record 2 that field actually -has- a value of blank  but it is different than null


a good way of eliminating empty strings and nulls is to use

where len(field) > 0

so

WHERE   myID NOT LIKE 'IN%' and len(myID) > 0

should work




0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Lee R Liddick JrReporting AnalystAuthor Commented:
This isn't making any sense.  If I just put the WHERE clause as MYid is null it gives me the records with a null value.  As soon as I add anything else to the statement whether it be the NOT LIKE 'IN%' or eliminating empty strings, whatever, it gives me nothing.  Not sure if this matters or not, but the myID field is a varchar field.
0
 
_agx_Commented:
> WHERE   myID NOT LIKE 'IN%'

As mentioned, NULL is a special value. In most modern databases, NULL is never equal to anything. Not even itself.  So you typically use the keywords   IS NULL or IS NOT NULL

SELECT *
FROM    myTable
WHERE   myID NOT LIKE 'IN%'
OR            myID IS NULL
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Correct AGX, I had other statements in my query too that I didn't think was causing any problems but combined with what I was doing here and using the 'AND' instead of the 'OR' it was bringing me back bad results.
I got it fixed now, with all of your comments.  I raised the point value on the question so I can provide all with the necessary points as deserved.
0
 
SidFishesCommented:
" _AND_ operator "

yea.. sorry that was my typo

0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Thank you all for your time as assistance!
0
 
SidFishesCommented:
one thing tho...

Agx's and Erik's won't deal with

2                    Jim

where the field is "empty" but not null which is why I suggest using len()
0
 
SidFishesCommented:
err sorry..no that's not right in this case

as empty with be   NOT LIKE 'IN%'


(but in other cases it may be an issue/useful)
0
 
_agx_Commented:
> Agx's and Erik's won't deal with

No, mine works correctly.   You may want to read the question again.  
         "I should get all records except for #4".  

Your query does not do that.
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Agreed SidFishes, but I still awarded you with the points as this will be helpful in the future...but combined, all three suggestions were useful and used.
0
 
SidFishesCommented:
points not an issue (but thx)


and apologies agx - doing too many things at once and not paying enough attention....but at least my spelling was not bad ;)



0
 
_agx_Commented:
> doing too many things at once and not paying enough attention.

Good .. it's your turn today (and not mine ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.