SQL WHERE Clause True or False values

Question

I have a table that contains a column showing whether an account is inactive.

The column values are either True or False... I only want the False items.

So I've included a clause saying: Where (account.inactive IN ("False"))

But I get a Operator/Operand type mismatch? error

I have been able to do other where clauses in this way but this one has me confused. Sorry if this is a newbie question - Thanks for the help.

Here is a copy of the query. It works fine except for the where clause:

SELECT 
account.accountno, 
account.title AS AccountName,
account.sortcode,

CAST(ICASE(account.sortcode = "A",10,
account.sortcode = "B",11,
account.sortcode = "C",13,
account.sortcode = "D",12,
account.sortcode = "E",14,
account.sortcode = "F",15,
account.sortcode = "K",20,
account.sortcode = "M",20,
account.sortcode = "N",20,
account.sortcode = "P",21,
account.sortcode = "Q",22,
account.sortcode = "R",23,
account.sortcode = "S",24,
account.sortcode = "L",30,
account.sortcode = "U",30,
account.sortcode = "V",30,
account.sortcode = "X",30,
account.sortcode = "Z",32,
account.sortcode = "^",33,
account.sortcode = "W",31,
account.sortcode = "Y",34,0) AS NUMERIC) AS GLCatCode,

account.inactive,

system.company AS CompanyName 

FROM ((((account
LEFT JOIN trans
  ON account.uniqueid = trans.accountid AND account.ledgerno=1 AND trans.trantype NOT IN (8) )
LEFT JOIN period
  ON trans.periodno = period.periodno)
LEFT JOIN usercurr
  ON account.cncycode = usercurr.code)
LEFT JOIN system
  ON system.uniqueid = system.uniqueid)

Where (account.inactive IN ("False"))

GROUP BY 
account.accountno, 
AccountName, 
account.sortcode, 
account.inactive,
CompanyName, 
GLCatCode

ORDER BY 
account.sortcode ,  account.accountno

@bksi Thank you for steering me in the right direction. I had a look at the data dictionary and it is logical type field with one character length. I tried a few different things and Where (account.inactive = 0) and it seems to work. — Dean

Hart CO Hart CO · Accepted Answer · 2014-03-18T22:43:47

Are you sure the value can ever be 'FALSE'? Check the values available for filtering:

SELECT DISTINCT account.inactive
FROM ((((account
LEFT JOIN trans
  ON account.uniqueid = trans.accountid AND account.ledgerno=1 AND trans.trantype NOT IN (8) )
LEFT JOIN period
  ON trans.periodno = period.periodno)
LEFT JOIN usercurr
  ON account.cncycode = usercurr.code)
LEFT JOIN system
  ON system.uniqueid = system.uniqueid)

SQL WHERE Clause True or False values

4 Answers