Geek Answers Handbook

How to solve Oracle ORA-01790?

I have two select statements connected by "union". Performing this statement, I received:

Error report: SQL error: ORA-01790: expression must have the same data type as the corresponding expression 01790. 00000 - expression must have the same data type as the corresponding expression "

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

+4
source share
7 answers

As I mentioned in the question, I would like to have SUGGESTIONS for troubleshooting my problem. What I did was include one column at a time in each select clause and found that I had a mismatch in the very last column of my SQL UNION. Thank you very much for your participation and help, but I knew that I have a type mismatch. THAT I did not know how to troubleshoot.

+1
source

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

+12
source

Here is what I found:

ORA-01790: expression must have the same data type as the corresponding expression

Cause: The SELECT list item corresponds to a SELECT list item with a different data type in a different query of the same specified expression.

Action: Ensure that all relevant SELECT list items have the same data types. Use the TO_NUMBER, TO_CHAR, and TO_DATE functions to perform explicit data transformations.

I have not seen your query, but I assume that one of them in your union does not select the same columns as the other.

+9
source

The error tells you that you are combining columns with different data types. There are oracle functions that convert one type to another (for example, "to_char"), you will have to convert the data types to a common format, or at least one to another. If you post the actual request / types, it may be more specific.

+3
source

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

+2
source

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

Techonthenet - ORA-01790

+1
source

Obviously, the problem for the poster was solved more than half a century ago, however, I wanted to tell everyone who reads this message, in search of help, so that the order of the selected properties (columns) should coincide with one combined operator for the next. It is not enough to simply combine names and data types, although this is, in a sense, the main reason. But due to the way Union instructions are processed in Oracle, you can get ORA-01790 error due to inconsistency in column ordering.

In my case, I had a query with UNION ALL of two options. One of them had a column named "generic_column_name" as the 25th element in the select element, and the other had the same column named "generic_column_name" of the same data type (I checked several ways using hard coding, as well as using forced data type conversions). However, the second choice had this element in 19th place, so all the columns from there were offset, and this caused error ORA-01790.

+1
source

More articles:


All Articles