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?

Answer 1

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

Answer 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.