UNION ALL 查询触发ORA-01790错误：表达式数据类型不匹配问题咨询

这个报错的核心原因很明确：UNION ALL要求所有子查询的对应列必须拥有完全一致的数据类型（包括类型类别、精度甚至长度），你的查询里就是因为用了空字符串' '代替不同类型的列，导致类型不匹配。

具体问题分析 #

看你的第一个子查询：

SELECT ch.channel_desc, t.calendar_year, co.country_region, ...

这里的t.calendar_year是数字类型（Oracle SH库中times.calendar_year为NUMBER类型），而第二个子查询对应位置用了字符串' '：

SELECT ch.channel_desc, t.calendar_year, ' ', ... -- 第12行的问题根源

字符串和数字类型不兼容，直接触发ORA-01790。同理，后面的子查询里用' '代替数字类型的calendar_year，也会因为同样的逻辑报错。

解决方案 #

你有两种可靠的修复方式，选哪种取决于你最终想要的输出格式：

方式1：用NULL代替空字符串

NULL的类型会自动适配对应列的类型，不会触发类型不匹配问题。修改后的查询如下：

REM 修复后的UNION ALL查询，使用NULL替代空字符串
Select * from (
 SELECT ch.channel_desc, t.calendar_year, co.country_region, TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
 GROUP BY (ch.channel_desc, t.calendar_year, co.country_region)
 UNION ALL
 SELECT ch.channel_desc, t.calendar_year, NULL, TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
 GROUP BY (ch.channel_desc, t.calendar_year)
 UNION ALL
 SELECT ch.channel_desc, NULL, NULL, TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
 GROUP BY (ch.channel_desc)
 UNION ALL
 SELECT NULL, NULL, NULL, TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
) ORDER BY 1 desc, 2 desc, 3 desc;

这种方式保持了原列的数据类型，输出时NULL会显示为空（具体显示样式取决于你的客户端工具设置）。另外我把IN ('2003')改成了IN (2003)，避免数字列的隐式类型转换，提升查询效率。

方式2：统一将所有列转为字符串类型

如果你希望输出的空值显示为空格而非NULL，可以把所有非字符串类型的列（比如calendar_year）转为字符串，然后用' '填充空值：

REM 修复后的UNION ALL查询，统一转为字符串类型
Select * from (
 SELECT ch.channel_desc, TO_CHAR(t.calendar_year), co.country_region, TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
 GROUP BY (ch.channel_desc, t.calendar_year, co.country_region)
 UNION ALL
 SELECT ch.channel_desc, TO_CHAR(t.calendar_year), ' ', TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
 GROUP BY (ch.channel_desc, t.calendar_year)
 UNION ALL
 SELECT ch.channel_desc, ' ', ' ', TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
 GROUP BY (ch.channel_desc)
 UNION ALL
 SELECT ' ', ' ', ' ', TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES
 FROM sh2.sales s, sh2.countries co, sh2.times t, sh2.channels ch
 WHERE s.time_id=t.time_id AND s.channel_id= ch.channel_id
 AND ch.channel_desc IN ('Direct Sales', 'Internet')
 AND t.calendar_year IN (2003)
 AND co.country_region IN ('Europe')
) ORDER BY 1 desc, 2 desc, 3 desc;

这种方式确保所有对应列都是字符串类型，空字符串' '可以正常使用，不会触发类型不匹配。

额外提示 #

• 优先推荐方式1，保持原数据类型在后续排序、二次计算等场景下更灵活；
• 如果你用的Oracle版本支持，可以考虑用GROUPING SETS来替代多层UNION ALL，代码会更简洁易维护。

内容的提问来源于stack exchange，提问作者Jal