blocks|key|1891225|text|正如我在问题中提到的，我希望得到一些关于如何解决我的问题的建议。我所做的是在每个select语句中一次启用一列，并发现我的SQL+UNION的最后一列不匹配。非常感谢你的参与和帮助，但我知道我的类型不匹配，我不知道的是如何排除故障。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1891226|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

As I mention in the question I'd like to have SUGGESTIONS for how to troubleshoot my problem.
What I've done is enabled one column at a time in each select statement and found that I had mismatch at the very last column of my SQL UNION. Thanks a lot for participating and helping me, but I knew I had type mismatch, WHAT I didn't know is how to troubleshoot.

blocks|key|1888946|text|如果不查看您的SQL，我会猜测您的联合列具有不同的数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1888947|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Without looking at your SQL, I would guess that you have columns being UNION'ed that have different data types.

blocks|key|1888965|text|以下是发现的结果：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1888966|ORA-01790:表达式必须与相应表达式具有相同的数据类型|offset|length|style|BOLD|1888967|原因：选择列表项对应于同一集合表达式的另一个查询中具有不同数据类型的选择列表项。|1888968|操作：检查所有相应的选择列表项是否具有相同的数据类型。使用TO_NUMBER、TO_CHAR和TO_DATE函数执行显式数据转换。|1888969|我没有看到您的查询，但我猜您的联合中的一个select与另一个select选择的列不同。|1888970|entityMap^0|0|0|A|0|0|3|0|0|3|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|Z|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|O|$]]

Here's what found:

ORA-01790: expression must have same datatype as corresponding expression

Cause: A SELECT list item corresponds to a SELECT list item with a different datatype in another query of the same set expression.

Action: Check that all corresponding SELECT list items have the same datatypes. Use the TO_NUMBER, TO_CHAR, and TO_DATE functions to do explicit data conversions. 

I haven't seen your query, but I am guessing that one select in your union is not selecting the same columns as the other.

blocks|key|1889051|text|显然，海报的问题在五年前就已经解决了，尽管如此，我还是想向任何阅读这篇文章寻求帮助的人指出，所选属性(列)的顺序必须从一个联盟声明到下一个联盟声明匹配。仅仅让名称和数据类型匹配是不够的，尽管这在某种意义上是根本原因。但是，由于在Oracle中处理Union语句的方式，只有列的顺序不匹配，才有可能出现ORA-01790错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1889052|在我的例子中，我有一个包含两个selects的UNION的查询。一个select具有名为"generic_column_name“的列作为select中的第25项，另一个select具有相同数据类型的名为"generic_column_name”的列(我通过硬编码和强制数据类型转换测试了几种方法)。然而，第二个select将该项放在第19位，因此从那里开始的所有列都发生了偏移，这触发了ORA-01790错误。|1889053|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Clearly the issue for the poster was solved over half a decade ago, nonetheless I wanted to point out to anyone reading this post in search of help that the order of the selected properties (columns) must match from one unioned statement to the next. It is not enough to simply have the names and the data types match, though that is in a sense the root cause. But due to the way the Union statements are handled in Oracle, it is possible to get the ORA-01790 error due to a mismatch in the ordering of columns only.

In my case, I had a query with a UNION ALL of two selects. One select had a column named "generic_column_name" as the 25th item in the select, and the other select had that same column named "generic_column_name" of the very same data type (I tested several ways through hard coding and also using forced data type conversions). However the second select had this item in the 19th place, so all of the columns from there on were offset and this triggered the ORA-01790 error.

blocks|key|644549|text|这个错误告诉您，您正在联合具有不同数据类型的列。有一些oracle函数可以将一种类型转换为另一种类型(例如"to_char")，您必须将数据类型转换为通用格式，或者至少将其中一种转换为另一种。如果你张贴实际的查询/类型，可能会更详细。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|644550|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The error is telling you that you're union-ing columns with different datatypes. There are oracle functions that will convert one type to another (e.g. "to_char"), you'll have to convert the datatypes into a common format, or at least one into the other. If you post the actual query/types it'd be possible to be more specifc.

blocks|key|1888982|text|您需要确保联合中相应的列具有相同的数据类型。最简单的方法是逐个注释掉列以缩小到列的范围，然后在其中一个列中使用显式类型转换函数以使类型匹配。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1888983|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You need to make sure that corresponding columns in your union have the same data type. The easiest way would be to comment out columns one by one to narrow down to the column and then use explicit type conversion function in one of them to make types match.

blocks|key|588326|text|您试图执行SELECT语句(可能是UNION或UNION+ALL)，但所有查询的结果列中都没有包含匹配的数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|588327|Techonthenet+-+ORA-01790|offset|length|588328|entityMap|0|LINK|mutability|MUTABLE|url|http://www.techonthenet.com/oracle/errors/ora01790.php^0|0|0|O|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You tried to execute a SELECT statement (probably a UNION or a UNION ALL), and all of the queries did not contain matching data types in the result columns.

<a href="http://www.techonthenet.com/oracle/errors/ora01790.php" rel="nofollow noreferrer">Techonthenet - ORA-01790</a>

blocks|key|2052693|text|今天我也遇到了同样的问题。问题出在union中，基本上是列的顺序问题|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2052694|entityMap^0|H|5|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

I had the same problem today. The issue is in the <code>union</code>, basically in the order of the columns

I have two select statements joined by "union". While executing that statement I've got:

Error report:
SQL Error: ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 - "expression must have same datatype as corresponding expression"

Maybe you can give me an advise on how to diagnose this problem?

How to resolve Oracle error ORA-01790?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有两个select语句通过"union“连接在一起。在执行该语句时，我得到了：错误报告: SQL错误: ORA- 01790 :表达式必须与相应的表达式01790具有相同的数据类型。00000 -“表达式必须与相应的表达式具有相同的数据类型”也许你可以给我一个关于如何诊断这个问题的建议？

问如何解决Oracle错误ORA-01790？
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何解决Oracle错误ORA-01790？EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何解决Oracle错误ORA-01790？
EN