Need help formatting syntax for SELECT query on a Bittype data field

[ca_nimrod]

:confused; I have been trying to build a SQL statement in my coldfusion page
that queries our SQL Server database for a bunch of records. I want to
eliminate a subset of those records based on the value of a field (the field
type in SQL Server is Bit). I have tried to compare the returned value to 0,
'0', '%0%' etc. I cannot seem to find the right syntax to compare it as a true
boolean (i.e., d.fieldname is TRUE).

This is driving me nuts because I am sure it is something really simple. I
cannot find anything online about how ColdFusion/SQL makes that boolean value
validation.

Any help is most appreciated, the code is below d.field3 is the Bit type field.

SELECT d.field1, d.field2, d.field3, dt.doctype, s.section
FROM document d
INNER JOIN DocumentTypes dt ON (d.doctype = dt.doctype)
INNER JOIN Section s ON (d.section = s.section)
WHERE d.field2 LIKE '%data1%'
OR d.field2 LIKE '%data2%'
OR d.field2 LIKE '%data3%'
AND d.field3 = 0

-ca-nimrod

[The ScareCrow]

You do have the correct syntax to compare a bit field

where bit field = 1
where bit field = 0

Are you getting an error, if so what is it ?

If your not getting the results you expect, break the query down. I would
suggest trying without the LIKE's first
SELECT d.field1, d.field2, d.field3, dt.doctype, s.section
FROM document d
INNER JOIN DocumentTypes dt ON (d.doctype = dt.doctype)
INNER JOIN Section s ON (d.section = s.section)
WHERE d.field3 = 0

See if this returns the correct result set.

Ken

[MikerRoo]

As stated by Ken, the syntax, as you posted it, is correct.

However, problems arise in CF when the bit comparison is against a CF variable.
EG AND d.field3 = #SomeYesNoVar#

In that case, you would use <CFQUERYPARAM> (which you should be doing anyway).

Or is the issue that your query works but returns the wrong results?

It looks like you might have wanted:
WHERE
( d.field2 LIKE '%data1%'
OR d.field2 LIKE '%data2%'
OR d.field2 LIKE '%data3%'
)
AND d.field3 = 0