How to resolve Oracle error ORA-01790?

Question

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?

Solution 1

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.

Solution 2

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

Solution 3

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.

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Spork]

The columns that are UNION'ed depend on the order, so you probably just have a mismatch in column order within the union.

[alexherm]

agree, wrong column order or a field is missing either in select or values section

[ajk]

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.

[Caique Andrade]

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.