英文:
Conversion error from string from change to table column and one line in query
问题
我有一个表格，包含多列。相关的列有：
- 一个叫做
Hours的NUMERIC列 - 一个叫做
Active的BOOLEAN列 - 一个叫做
Type的VARCHAR列
Type 原本也是 BOOLEAN 类型，直到我需要超过两个可能值时，我将该列改为了 VARCHAR 类型。
我有一个原本有效的大型查询。以下是我修改的查询的一部分：
(
SELECT SUM("Hours")
FROM "Table"
WHERE
(
"Type" = 'B'
AND "Active" = TRUE
)
) AS "Label",
上述查询部分的唯一更改是从 "Type" = TRUE 改为了 "Type" = "B"。没有其他行发生改变。现在当我运行查询时，我收到以下错误：
> 字符串 "A" 的转换错误
如果我将同一行改为 "Type" = "A"，或者 "Type" = "C"，错误会变为：
*列未知
*A
或者：
*列未知
*C
我的目标是在 Table 中对 Active 为 TRUE，且为指定 Type 的行的 Hours 进行求和。我不确定为什么一个看似简单的查询会失败。
英文:
I have a table with multiple columns. The relevant columns are:
- A
NUMERICcolumn calledHours - A
BOOLEANcolumn calledActive - A
VARCHARcolumn column calledType
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.
答案1
得分: 1
第一个问题是，在条件 "Type" = TRUE 中，Firebird 会将列 Type 的值强制转换为布尔值，而不是将 TRUE 转换为字符串。这会导致对于不是 'TRUE' 或 'FALSE'（不区分大小写）的值失败。你需要使用 = 'TRUE' 而不是 = TRUE。
至于第二个问题，在标准 SQL 和 Firebird（至少在方言 3 中，Firebird 的默认方言）中，双引号用于界定标识符，而不是字符串。你需要使用 'A' 和 'C'，而不是 "A" 或 "C" 作为字符串。当你使用 "A" 时，Firebird 将其视为对列 A 的引用，而在这种情况下，该列不存在。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论