ORA-01790 means the queries combined by UNION, UNION ALL, INTERSECT, or MINUS return columns of different data type groups in the same position. Each column of the result needs one type, so a number in the first query and a string in the second, or a date and a number, cannot be stacked. Convert one side so both have the same type.
Reproduce the Error
Example:
select 1 as val from dual union all select 'one' from dual; select sysdate as val from dual union select 0 from dual;
Output:
Error starting at line : 1 In command - select 1 as val from dual union all select 'one' from dual Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-01790: expression must have same datatype as corresponding expression Error starting at line : 5 In command - select sysdate as val from dual union select 0 from dual Error at Command Line : 5 Column : 8 Error report - SQL Error: ORA-01790: expression must have same datatype as corresponding expression
Fix: Convert to a Common Type
Example:
select to_char(1) as val from dual union all select 'one' from dual; select sysdate as val from dual union select to_date(null) from dual;
Output:
VAL ______ 1 one VAL ______________ 06-OCT-2026
- TO_CHAR turns the number into text, so both rows are strings.
- A NULL placeholder must have the right type too: TO_DATE(NULL) is a date, while a bare 0 was a number. A plain NULL also works, as it takes the type of the other query.
Things to Know
- The column names of the result come from the first query; the types must agree in every query.
- Numbers of different kinds, such as NUMBER and BINARY_DOUBLE, are combined without an error.
- A different number of columns raises ORA-01789 instead.
Related Guides
Conclusion
ORA-01790 means a column of a set operation has different data types in different queries. Convert explicitly so each position has one type in every branch, including NULL placeholders.
