英文:
Casting int problem inside a nested FROM clause subquery
问题
我有一个带有主键参数 "code" 的表，数据类型为字符串。该键可以是数字或字母数字混合的。我的想法是编写一个查询，以在范围内获取键的数字值中的最大值。
考虑一个名为 FooTable 的表，其中包含以下记录的键值: "abc", "def", "ghi", "10", "12", "30"。
首次尝试不包含范围子句，如下所示:
SELECT MAX(Ex.code) AS maxValue FROM
(SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex
结果: 在 maxValue 列下有一个值为 30 的记录。
然后，我添加了包含范围的 WHERE 子句，但 SQL Server 报错:
SELECT MAX(Ex.code) FROM
(SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex
WHERE Ex.code < 13
错误: 错误: Msg 245 - 将 varchar 值 'abc' 转换为数据类型 int 时发生转换失败。
为什么外部查询使用了 FooTable，而不是内部嵌套查询的结果集呢?
注意: 这种替代解决方案有效，但涉及多个操作。
DECLARE @Ex TABLE (code int);
INSERT INTO @Ex SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1;
SELECT max(code) AS maxValue FROM @Ex WHERE code < 13;
结果: 在 maxValue 列下有一个值为 12 的记录。
提前感谢您的答复。
英文:
I have a table with the primary key param "code" typed as string. The key could be numeric or alphanumeric. The idea is to write a query that gives the max value among the numeric values of the keys inside a range.
Consider a table called FooTable with these values as key for these records: "abc", "def", "ghi", "10", "12", "30".
A first try without the range clause works and it's the following:
SELECT MAX(Ex.code) AS maxValue FROM
(SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex
> Result: 1 record with value 30 under column maxValue.
Then I inserted the WHERE clause with the range and SQL Server printed this error:
SELECT MAX(Ex.code) FROM
(SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1) AS Ex
WHERE Ex.code < 13
> Error: Error: Msg 245 – Conversion failed when converting the varchar value ‘abc’ to data type int.
How is it possible that the external query uses the FooTable and not the FROM nested query result set?
Note: This alternative solution works but it uses more than one operation.
DECLARE @Ex TABLE (code int);
INSERT INTO @Ex SELECT CAST(code AS int) AS code FROM FooTable WHERE ISNUMERIC(code) = 1;
SELECT max(code) AS maxValue FROM @Ex WHERE code < 13;
> Result: 1 record with value 12 under column maxValue.
Thanks in advance for the answers.
答案1
得分: 1
当我使用TRY_CAST时，它起作用：
DECLARE @FooTable TABLE
(
code VARCHAR(10)
);
INSERT INTO @FooTable
(
code
)
VALUES
('abc'),
('def'),
('ghi'),
('10'),
('12'),
('30');
SELECT MAX(Ex.code) AS maxValue
FROM
(
SELECT TRY_CAST(code AS INT) AS code
FROM @FooTable
) AS Ex
WHERE ex.code < 13
英文:
When I use TRY_CAST it works:
DECLARE @FooTable TABLE
(
code VARCHAR(10)
);
INSERT INTO @FooTable
(
code
)
VALUES
('abc'),
('def'),
('ghi'),
('10'),
('12'),
('30');
SELECT MAX(Ex.code) AS maxValue
FROM
(
SELECT TRY_CAST(code AS INT) AS code
FROM @FooTable
) AS Ex
WHERE ex.code < 13
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论