sql - How to resolve Oracle error ORA-01790?

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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?

The columns that are UNION'ed depend on the order, so you probably just have a mismatch in column order within the union. – Spork Nov 23, 2015 at 10:43

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.

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.

Higher voted answers don't address this scenario, thanks for reviving. Both the data types AND the order must match. Oracle docs don't make this clear they specify that the type and number of columns must match. – ajk Aug 28, 2020 at 16:01

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.

As posted by @FrustratedWithFormsDesigner, check the following 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. – Caique Andrade Feb 11, 2022 at 13:57

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.

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

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question . Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers .