Bit Data Type

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.

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.

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

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

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.

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.

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

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:

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

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.

SORTED. Square brackets around [View] helped.

Thanks everyone for efforts.

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

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