Oracle/PLSQL: ORA-01790 Error

Error Message

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

Cause of Error

You tried to execute a SELECT statement (probably an SQL UNION query or an SQL UNION ALL query), and all of the queries did not contain matching data types in the result columns.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Re-write the SELECT statement so that each matching column is the same data type.

For example, if you tried to execute the following UNION query:

select supplier_name
from orders
UNION
select quantity
from orders_audit;

You would receive an error message as follows:

Since the supplier_name column is defined as a varchar2 and the quantity column is defined as a number, these columns can not be matching columns.

You can try correcting this UNION query by using a conversion function (ie: to_char function, to_number function, or to_date function) to convert the values to matching data types.

For example:

select supplier_name
from orders
UNION
select to_char(quantity)
from orders_audit;

In this example, we've used the to_char function to convert the quantity column to a data type that is compatible with the supplier_name column.