• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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