主页 / dba / 问题 / 345458
Accepted
Sergey Zolotarev
Asked: 2025-02-26 20:04:41 +0800 CST

没有使用 WHEN 的隐式类型转换

  • 772

我们的应用程序有时必须动态创建 SQL 查询，包括ORDER BY类似这样的子句：

ORDER BY CASE t.ID
    WHEN 895 THEN 1
    WHEN 643 THEN 2
END

问题t.ID通常出在NUMBER列上 — — 但很少出在VARCHAR列上（取决于表，提醒您，表是动态的）。

当发生这种情况时，我们会收到如下错误（下面是本地化版本，但很容易弄清楚）：

SQL 错误 [932] [42000]: ORA-00932: несовместимые типы данных: ожидается CHAR, получено NUMBER

尽管我们的 RDBMS 可以执行隐式类型转换，但显然它无法工作WHEN（出于某种原因）。我在文档中没有找到任何关于它的提及（欢迎您提供这样的参考）。

以下是复现脚本（该示例确实有些牵强）：

create table "user"(
  id VARCHAR(50),
  name VARCHAR(50)
);

insert into "user"
values ('1', 'John Doe'),
  ('2', 'John Roe'),
('3', 'Jane Doe');

select * from "user"
order by case id
  when 1 then 1
  when 2 then 2
end;

用这个替换最后一个脚本以使其工作

select * from "user"
order by case id
  when '1' then 1
  when '2' then 2
end;

您的建议是什么？

Oracle DB、Postgres（我们两者都使用）。

¹除了“永远不要依赖隐式类型转换”

postgresql

3 个回答

  1. 我的建议是始终转换为字符串：

    ORDER BY CASE CAST (t.ID AS character varying(4000))
    WHEN '895' THEN 1
    WHEN '643' THEN 2
END

    那么它应该适用于数字和字符串，因为所有数字都可以转换为字符串。

    • 3
  2. Best Answer

    这两种 RDBMS 是否都支持转换语法？

    SELECT * 
FROM "user"
ORDER BY CASE 
  WHEN COALESCE(CAST(id AS VARCHAR(100)), '') = '1' THEN 1
  WHEN COALESCE(CAST(id AS VARCHAR(100)), '') = '2' THEN 2
END;

    根据您的需要调整长度。

    注意。如果您确定id永远不会为空，COALESCE则可以删除

    • 3

  3. 这是 Oracle 数据库不进行隐式转换的少数情况之一。 摘自文档：

    对于简单的 CASE 表达式，expr 和所有比较表达式值必须具有相同的数据类型（CHAR、VARCHAR2、NCHAR 或 NVARCHAR2、NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE）或必须全部具有数字数据类型。如果所有表达式都具有数字数据类型，则 Oracle 确定具有最高数字优先级的参数，隐式将其余参数转换为该数据类型，并返回该数据类型。

    请注意，尽管字符串“1”可以转换为数字，但这两种方法都会失败：

    select case '1' when 1 then 'nope' end;
ORA-00932: expression (1) is of data type NUMBER, which is incompatible with expected data type CHAR 
select case 1 when '1' then 'nope' end;
ORA-00932: expression ('1') is of data type CHAR, which is incompatible with expected data type NUMBER

    为了解决这个问题，您可以转换表达式。在 Oracle 数据库中，搜索的案例表达式将进行隐式转换：

    select case when '1' = 1 then 'yep' end;

CAS
---
yep

select * from "user"
order by case 
  when id = 1 then 1
  when id = 2 then 2
end;

ID                                                 NAME                                              
-------------------------------------------------- --------------------------------------------------
1                                                  John Doe                                          
2                                                  John Roe                                          
3                                                  Jane Doe
    • 2

相关问题