Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Testing if bit field = 1

I have a field 'del' in a table, 'mytable',  defined as type 'bit'. It is also nullable. I have two rows, one with del=1, and one with del=NULL. In my select statement I have


select * from mytable
where del<>1

but I am returned no rows. Can i test a bit field this way?

thx

Rich
0
RichardFox
Asked:
RichardFox
  • 3
  • 2
1 Solution
 
RichardFoxAuthor Commented:
Oh, and if I use

select * from mytable
where del=1

the row is returned. So del=1 appears to work but del<>1 doesn't
0
 
RichardFoxAuthor Commented:
And a little bit more (excuse the pun):

If a row has del set to zero,

select * from mytable
where del<>1

returns the row. BUT, it does not return rows where del is NULL! Why? Definitely NULL is <> 1!
0
 
arbertCommented:
This is how null works--straight from books online:

Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:
0
 
arbertCommented:

select * from mytable
where del<>1 or del is null
0
 
RichardFoxAuthor Commented:
Thanks for the education, I appreciate it
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now