Search code examples
sqlfirebird

Conversion error from string from change to table column and one line in query

I have a table with multiple columns. The relevant columns are:

  1. A NUMERIC column called Hours
  2. A BOOLEAN column called Active
  3. A VARCHAR column column called Type

Type used to also be BOOLEAN until I needed more than two possible values, so I changed the column to VARCHAR

I have a large query that used to work. Below is the part of the query I modified:

(
    SELECT SUM("Hours")
    FROM "Table"
    WHERE
        (
            "Type" = "B"
            AND "Active" = TRUE
        )
) AS "Label",

The only change in the above query section being from "Type" = TRUE to "Type" = "B". No other line was changed. Now when I run the query, I receive the following error:

Conversion error from string "A"

If I change that same line to "Type" = "A", or "Type" = "C", the error changes to:

*column unknown
*A

Or:

*column unknown
*C

My goal is to sum up Hours in Table, where the rows are TRUE for Active and of the specified Type. I'm not sure why an otherwise (seemingly) simple query fails.

Solution

  • The first problem is that in the condition "Type" = TRUE, Firebird will coerce the values of column Type to a Boolean instead of converting TRUE to a string. This fails for values that are not 'TRUE' or 'FALSE' (case-insensitive). You need to use = 'TRUE' instead of = TRUE.

    As to the second problem, double quotes in standard SQL and Firebird (at least in dialect 3, Firebird's default dialect) delimit identifiers, not strings. You need to use 'A' and 'C', not "A" or "C" for strings. When you use "A", Firebird sees it as a column reference to a column A, which in this case doesn't exist.