ORA-01790: expression must have same datatype as corresponding expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

ORA-01790: expression must have same datatype as corresponding expression

This error message can be raised when you are performing a union of two queries, as follows:

create table test1_1790 (
col_a varchar2(30),
col_b number,
col_c date);

create table test2_1790 (
col_a varchar2(30),
col_c date,
col_b number);

select * from test1_1790
union all
select * from test2_1790;

ORA-01790: expression must have same datatype as corresponding expression

As you see the root cause of the error is in the mismatching column ordering that is implied by the use of * as column list specifier. This type of errors can be easily avoided by entering the column list explicitly:

select col_a, col_b, col_c from test1_1790
union all
select col_a, col_b, col_c from test2_1790;

A more frequent scenario for this error is when you inadvertently swap (or shift) two or more columns in the SELECT list:

select col_a, col_b, col_c from test1_1790
union all
select col_a, col_c, col_b from test2_1790;

ORA-01790: expression must have same datatype as corresponding expression

See message translations for ORA-01790 and search additional resources.