How to resolve Oracle error ORA-01790?

问题:

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?

回答1:

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

回答2:

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.

回答3:

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.

回答4:

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.

回答5:

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.

Techonthenet - ORA-01790

回答6:

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.

回答7:

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.