How to filter on boolean fields in Firebird

[dougmn]

I’m experimenting with migration of a database to Firebird from HSQLDB. I have a personal accounting database with a boolean field “Deductible.” When filtering for reports, I want to be able to select one of the following options:

Only decuctible (Deductible field = TRUE)

Only NOT deductible (Deductible field = FALSE)

ALL records (Dedcutble field is either TRUE or FALSE)

I have a 1 record table for my filtering criteria, selecting for dates, expense category, as well as the deductible field. I use a form to update this table. A query based on the data table and the criteria table is the basis for reports.

With HSQLDB I could use integer values to filter for boolean fields, but this doesn’t seem to work in Firebird. How do I set up my criteria table and form to filter for the three options I want above? The “ALL records” option is what I don’t know how to select for.

[dougmn]

Okay, I’ll answer my own question. I suppose writing it out, and then having some time to (not) think about it helped.

What I did was create a column in the query that converted the boolean values to integer: IIF(“Deductible” = FALSE,0,1). Then I used the integer values from the filter table as criteria. For only the deductible records, the criteria is <>0, for only NOT deductible the criteria is <>1, and for ALL records the criteria is <>2.

Feel free to comment or add a different solution if you like.

[Ratslinger]

Hello,

While I agree with your answer, it doesn’t cover all states. If the field is not specified with a NOT NULL constraint then there is a third possibility of unknown. See BOOLEAN Data Type. This portion kept me from responding as I still haven’t arrived with a solution.

[dougmn]

Yes, including the “unknown” values would make it more complex. It could probably be solved with an additional line of criteria (an “OR” in the SQL) in the query. In my case I would prefer to prevent any of the boolean fields from having the value of “unknown” – reslove it at the point of data entry. And I just learned from your comment @Ratslinger that I can do that by assigning a NOT NULL constraint to the field definition.