DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01790 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

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

ORA-01790 is a common Oracle error that occurs when you use set operators — such as UNION, UNION ALL, INTERSECT, or MINUS — and the columns at the same position in each SELECT statement have incompatible data types. Oracle requires that every corresponding column across all query blocks in a set operation must share a compatible data type. This error is straightforward once you understand the root cause, and it can always be resolved with explicit type conversion.

Top 3 Causes

1. Mismatched Column Order in UNION / UNION ALL

The most frequent cause is accidentally placing columns of different types at the same position when combining two queries.

-- ERROR: employee_id is NUMBER, dept_name is VARCHAR2
SELECT employee_id, hire_date FROM employees
UNION ALL
SELECT dept_name, location_id FROM departments;
-- ORA-01790 thrown on column 1 (NUMBER vs VARCHAR2)

-- FIX: Align data types using explicit conversion
SELECT TO_CHAR(employee_id) AS col1, TO_CHAR(hire_date, 'YYYY-MM-DD') AS col2
FROM employees
UNION ALL
SELECT dept_name, TO_CHAR(location_id)
FROM departments;
Enter fullscreen mode Exit fullscreen mode

2. Mixing DATE and VARCHAR2 (Common in Legacy Integrations)

In real-world projects, the same logical field (e.g., order date) may be stored as DATE in one table and VARCHAR2 in another — especially when integrating legacy and modern systems.

-- ERROR: DATE vs VARCHAR2 on order_date column
SELECT order_id, order_date FROM new_orders   -- DATE type
UNION
SELECT order_id, order_date FROM old_orders;  -- VARCHAR2 type
-- ORA-01790 on column 2

-- FIX option 1: Convert VARCHAR2 to DATE
SELECT order_id, order_date FROM new_orders
UNION
SELECT order_id, TO_DATE(order_date, 'YYYY-MM-DD') FROM old_orders;

-- FIX option 2: Convert both to VARCHAR2 (for reporting)
SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD') FROM new_orders
UNION
SELECT order_id, order_date FROM old_orders;
Enter fullscreen mode Exit fullscreen mode

3. CASE / NVL Expressions Returning Mixed Types

CASE WHEN and NVL expressions can silently return mixed types when THEN and ELSE branches return different data types, causing ORA-01790 in set operations.

-- ERROR: CASE returns NUMBER in THEN, VARCHAR2 in ELSE
SELECT CASE WHEN flag = 1 THEN 100 ELSE 'NONE' END AS result
FROM table_a
UNION ALL
SELECT score FROM table_b;
-- ORA-01790 because CASE expression resolves to conflicting types

-- FIX: Make all CASE branches the same type
SELECT CASE WHEN flag = 1 THEN '100' ELSE 'NONE' END AS result
FROM table_a
UNION ALL
SELECT TO_CHAR(score) FROM table_b;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Use TO_CHAR() to convert NUMBER or DATE to VARCHAR2 for uniformity.
  • Use TO_NUMBER() or CAST(col AS NUMBER) when you need numeric results.
  • Use TO_DATE() with an explicit format mask to convert VARCHAR2 to DATE.
  • Always verify column types before writing set operation queries: 
-- Check column types before writing UNION queries
SELECT column_name, data_type, column_id
FROM user_tab_columns
WHERE table_name IN ('TABLE_A', 'TABLE_B')
ORDER BY column_id, table_name;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always verify data types before using set operators. Query USER_TAB_COLUMNS or ALL_TAB_COLUMNS to compare column types across tables before writing any UNION-based query. Make this a standard step in your SQL development checklist.

  2. Use explicit type conversion as a team convention. Never rely on Oracle's implicit conversion. Enforce the use of TO_CHAR(), TO_NUMBER(), TO_DATE(), and CAST() in all SQL code reviews. Add a specific checklist item — "Are all UNION column types aligned?" — to your peer review process to catch this error before it hits production.

Related Errors

  • ORA-01789: Raised when the number of columns in each SELECT of a set operation does not match — often appears alongside ORA-01790.
  • ORA-01722: invalid number — can occur when fixing ORA-01790 by using TO_NUMBER() on a column containing non-numeric strings.
  • ORA-01858: a non-numeric character found where a numeric was expected — may appear when using TO_DATE() with an incorrect format mask during ORA-01790 resolution.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)