SQLTeam.com | Weblogs | Forums

Bit Data Type


#1

I'm joining two tables, where second table consist of bit data type, which I was hoping to filter by, but cannot figure out correct syntax.

SELECT *
FROM [Table1]
JOIN [Table2] on [Table2].ID = [Table1].ID

WHERE [Table2].Column1 = 'test'
AND [Table2].Column2 = 1

Column1 is varchar, Column2 is bit,null. It fails on Column2.


#2

What part is failing? If Column2 is NULL then, then Column2 = 1 will return false and the row won't be returned.


#3

As I said it fails on column2 and says incorrect syntax near column2.


#4

Ah, yes you did say "syntax". Hmmm. That looks right to me. BIT should be 1 or 0 in a WHERE clause or assignment clause. Unless it's a typo or I'm missing something obvious I'm not sure what it is.


#5

BIT is not an INTEGER, and to be precise it can be 1, 0 or null, INTEGER cannot be null. So BIT has to be treated differently, but I'm not sure what is correct syntax here.


#6

Try this
Coalesce( [Table2].Column2,-1) = 1


#7

That is proper syntax. The syntax error has to be somewhere else. Often a missing "END" statement or other syntax error isn't caught by the parser until later in the SQL. O the column name is a reserved word, bu that should give a more specific syntax error.

Btw, since those conditions must be true in order to JOIN, you could add them to the ON clause instead of using WHERE:

SELECT *
FROM [Table1]
JOIN [Table2] on [Table2].ID = [Table1].ID AND [Table2].Column1 = 'test' AND [Table2].Column2 = 1


#8

None of the suggestions above worked :frowning:

Here is actual query. If BIT is being treated same as INT then I cannot see how this could fail.


#9

SORTED. Square brackets around [View] helped.

Thanks everyone for efforts.


#10

Yes because the word view is a reserved word, which you never told us about. glad you sorted it out.


#11

I was sure it was down to BIT type not being equal to INT.