SQL 如何解决 Oracle 错误 ORA-01790？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1863112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve Oracle error ORA-01790?
提问by Roman Kagan
I have two select statements joined by "union". While executing that statement I've got:
我有两个由“union”连接的选择语句。在执行该语句时，我得到了：
Error report: SQL Error: ORA-01790: expression must have same datatype as corresponding expression 01790. 00000 - "expression must have same datatype as corresponding expression"
错误报告：SQL 错误：ORA-01790：表达式必须与相应的表达式 01790 具有相同的数据类型。00000 - “表达式必须与相应的表达式具有相同的数据类型”
Maybe you can give me an advise on how to diagnose this problem?
也许你可以给我一个关于如何诊断这个问题的建议？
采纳答案by Roman Kagan
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.
正如我在问题中提到的，我想对如何解决我的问题提出建议。我所做的是在每个 select 语句中一次启用一列，并发现我的 SQL UNION 的最后一列不匹配。非常感谢参与和帮助我，但我知道我有类型不匹配，我不知道如何进行故障排除。
回答by Otávio Décio
Without looking at your SQL, I would guess that you have columns being UNION'ed that have different data types.
不看你的 SQL，我猜你有被联合的列有不同的数据类型。
回答by FrustratedWithFormsDesigner
Here's what found:
这是发现的内容：
ORA-01790:expression must have same datatype as corresponding expression
ORA-01790:表达式必须与相应的表达式具有相同的数据类型
Cause:A SELECT list item corresponds to a SELECT list item with a different datatype in another query of the same set expression.
原因：SELECT 列表项对应于同一集合表达式的另一个查询中具有不同数据类型的 SELECT 列表项。
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.
行动：检查所有相应的选择列表项具有相同的数据类型。使用 TO_NUMBER、TO_CHAR 和 TO_DATE 函数执行显式数据转换。
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.
我还没有看到您的查询，但我猜您联合中的一个选择没有选择与另一个相同的列。
回答by markmacw
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.
很明显，海报的问题在 50 多年前就已经解决了，尽管如此，我还是想向任何阅读这篇文章以寻求帮助的人指出，所选属性（列）的顺序必须从一个联合语句到下一个匹配。仅仅让名称和数据类型匹配是不够的，尽管这在某种意义上是根本原因。但是由于在 Oracle 中处理 Union 语句的方式，可能会由于列的顺序不匹配而出现 ORA-01790 错误。
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.
就我而言，我有一个带有两个选择的 UNION ALL 的查询。一个选择有一个名为“generic_column_name”的列作为选择中的第 25 项，另一个选择有一个名为“generic_column_name”的相同数据类型的列（我通过硬编码测试了几种方法，也使用强制数据类型转换）。然而，第二个选择在第 19 位有这个项目，所以从那里开始的所有列都被偏移，这触发了 ORA-01790 错误。
回答by Steve B.
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.
该错误告诉您您正在合并具有不同数据类型的列。有一些 oracle 函数可以将一种类型转换为另一种类型（例如“to_char”），您必须将数据类型转换为通用格式，或者至少将一种转换为另一种格式。如果您发布实际的查询/类型，则可能会更加具体。
回答by Eugene Kuleshov
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.
您需要确保联合中的相应列具有相同的数据类型。最简单的方法是将列一一注释掉以缩小到列的范围，然后在其中之一中使用显式类型转换函数来使类型匹配。
回答by OMG Ponies
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.
您尝试执行 SELECT 语句（可能是 UNION 或 UNION ALL），但所有查询在结果列中都不包含匹配的数据类型。
回答by Gabriel Batista
I had the same problem today. The issue is in the union, basically in the order of the columns
我今天遇到了同样的问题。问题出在union，基本上按照列的顺序
