While using set operators
like UNION, UNION ALL etc, we run into the following error
ORA-01790: expression must
have same datatype as corresponding expression. The cause of this error is
illustrated below.
2
union
3
select null from dual ;
select 1 from dual
*
ERROR at line 1:
ORA-01790: expression
must have same datatype as corresponding expression
Set operators demand that
all the selects in the statements have the same number of columns, and also
similar respect datatypes.
But usually, when we don’t
have a corresponding column in a select, we just select ‘NULL’ to fill that place in the respective select.
Once we do that we potentially run into the above illustrated error.
In order to overcome
ORA-01790 , we have to do the below, i.e. convert the null into the datatype of
the respective corresponding columns.
SQL> select 1 from
dual
2
union
3
select to_number(null) from dual;
1
----------
1