[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CFQUERY not capturing records with 'NULL' values

Posted on 2009-04-30
14
Medium Priority
?
250 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:Lee R Liddick Jr
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 19

Assisted Solution

by:erikTsomik
erikTsomik earned 400 total points
ID: 24271506
can you do this
<cfquery datasource="myDatasource" name="myQuery">
SELECT *
FROM    myTable
WHERE   myID NOT LIKE 'IN%' or myID is null
</cfquery>
0
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 400 total points
ID: 24271752
"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
 

Author Comment

by:Lee R Liddick Jr
ID: 24272311
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:_agx_
ID: 24272356
> 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
 
LVL 52

Accepted Solution

by:
_agx_ earned 800 total points
ID: 24272399
>  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
 

Author Comment

by:Lee R Liddick Jr
ID: 24272449
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24272456
" _AND_ operator "

yea.. sorry that was my typo

0
 

Author Closing Comment

by:Lee R Liddick Jr
ID: 31576502
Thank you all for your time as assistance!
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 24272474
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24272503
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
 
LVL 52

Expert Comment

by:_agx_
ID: 24272514
> 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
 

Author Comment

by:Lee R Liddick Jr
ID: 24272520
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24272558
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
 
LVL 52

Expert Comment

by:_agx_
ID: 24272665
> doing too many things at once and not paying enough attention.

Good .. it's your turn today (and not mine ;)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

872 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