ORA-01790: Expression Must Have Same Datatype - Fix UNION

ORA-01790: Expression Must Have Same Datatype as Corresponding Expression

Error Overview

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

The ORA-01790 error occurs when Oracle detects incompatible data types in positions that require type alignment. The most common triggers are set operators (UNION, UNION ALL, INTERSECT, MINUS) where corresponding columns in each SELECT branch have different data types, and CASE or DECODE expressions where the result branches return incompatible types. Oracle cannot implicitly convert between certain types — such as DATE and VARCHAR2, or NUMBER and CLOB — so the statement fails at parse time.

Common Causes

1. UNION / UNION ALL Column Type Mismatch

• One SELECT branch returns a DATE, the other returns a VARCHAR2 for the same column position
• Numeric column in one branch paired with a character column in another
• CLOB or BLOB column not consistently typed across all SELECT branches

2. CASE or DECODE Result Type Conflict

• WHEN branches return different data types (e.g., one returns a NUMBER, another a string)
• ELSE clause returns a type incompatible with the THEN branches
• Implicit conversion rules do not cover the specific type pair

3. INTERSECT or MINUS with Mixed Types

• Same root cause as UNION: corresponding columns must have compatible types
• Aggregate results joined with raw column values of different types

4. Subquery Column Type Inconsistency

• Inline view or subquery in a UNION returns a different type for a derived column
• NVL or COALESCE mixing a NULL placeholder of one type with an expression of another

5. Schema Changes Breaking Existing Queries

• A column’s data type changed (e.g., DATE promoted to TIMESTAMP) without updating dependent UNION queries
• View redefined with additional columns shifting column positions

Diagnostic Queries

Check Column Types in a UNION Query

-- Examine column data types for each branch of a UNION manually
-- Step 1: describe the first SELECT branch via a view
CREATE OR REPLACE VIEW v_branch1 AS
  SELECT col1, col2, col3 FROM table_a WHERE 1=0;

CREATE OR REPLACE VIEW v_branch2 AS
  SELECT col1, col2, col3 FROM table_b WHERE 1=0;

-- Compare types side by side
SELECT
  a.column_name,
  a.data_type AS branch1_type,
  b.data_type AS branch2_type,
  CASE WHEN a.data_type != b.data_type THEN 'MISMATCH' ELSE 'OK' END AS status
FROM user_tab_columns a
JOIN user_tab_columns b ON a.column_name = b.column_name
WHERE a.table_name = 'V_BRANCH1'
  AND b.table_name = 'V_BRANCH2'
ORDER BY a.column_id;

Find Tables with Mixed-Type Columns of the Same Name

-- Find columns with the same name but different types across tables
SELECT
  column_name,
  COUNT(DISTINCT data_type) AS type_count,
  LISTAGG(table_name || '(' || data_type || ')', ', ')
    WITHIN GROUP (ORDER BY table_name) AS tables_and_types
FROM user_tab_columns
WHERE column_name IN (
  SELECT column_name
  FROM user_tab_columns
  GROUP BY column_name
  HAVING COUNT(DISTINCT data_type) > 1
)
GROUP BY column_name
ORDER BY column_name;

Investigate CASE Expression Types

-- Test individual CASE branches in isolation
SELECT
  CASE WHEN 1=1 THEN SYSDATE END AS date_result,
  DUMP(CASE WHEN 1=1 THEN SYSDATE END) AS type_info
FROM dual;

-- Check DUMP output to identify actual internal type codes
-- Type 1 = VARCHAR2, Type 2 = NUMBER, Type 12 = DATE, Type 180 = TIMESTAMP
SELECT DUMP(your_column) FROM your_table WHERE ROWNUM = 1;

Step-by-Step Resolution

1. Align Types in UNION Queries Using CAST

The cleanest fix is to explicitly cast mismatched columns to a common type in every branch:

-- BAD: DATE in branch 1, VARCHAR2 in branch 2
SELECT employee_id, hire_date       FROM employees
UNION
SELECT customer_id, signup_date_str FROM customers;  -- signup_date_str is VARCHAR2

-- GOOD: convert VARCHAR2 to DATE in branch 2
SELECT employee_id, hire_date
FROM employees
UNION
SELECT customer_id, TO_DATE(signup_date_str, 'YYYY-MM-DD')
FROM customers;

-- GOOD: convert both to VARCHAR2 if date arithmetic is not needed
SELECT employee_id, TO_CHAR(hire_date, 'YYYY-MM-DD') AS event_date
FROM employees
UNION
SELECT customer_id, signup_date_str
FROM customers;

2. Use CAST for Precision Control

-- CAST aligns types explicitly and makes intent clear
SELECT
  product_id,
  CAST(unit_price AS NUMBER(10,2)) AS amount,
  'PRODUCT' AS source
FROM products
UNION ALL
SELECT
  order_id,
  CAST(total_amount AS NUMBER(10,2)) AS amount,
  'ORDER' AS source
FROM orders;

-- Align character lengths
SELECT CAST(short_code AS VARCHAR2(50)) AS code FROM table_a
UNION
SELECT long_description AS code FROM table_b;

3. Fix CASE / DECODE Type Conflicts

-- BAD: mixing DATE and NULL without explicit type
SELECT
  CASE status
    WHEN 'ACTIVE'   THEN hire_date       -- DATE
    WHEN 'INACTIVE' THEN NULL            -- unknown type, Oracle infers from first branch
    ELSE            NULL
  END AS reference_date
FROM employees;
-- This works, but adding a VARCHAR2 branch would fail:

-- BAD: adding a VARCHAR2 branch to a DATE CASE
SELECT
  CASE status
    WHEN 'ACTIVE'   THEN hire_date
    WHEN 'PENDING'  THEN 'TBD'           -- VARCHAR2 — causes ORA-01790
  END AS reference_date
FROM employees;

-- GOOD: cast all branches to the same type
SELECT
  CASE status
    WHEN 'ACTIVE'   THEN TO_CHAR(hire_date, 'YYYY-MM-DD')
    WHEN 'PENDING'  THEN 'TBD'
    ELSE                 'UNKNOWN'
  END AS reference_date
FROM employees;

4. Fix NULL Placeholder Types in NVL / COALESCE

-- BAD: NVL with incompatible second argument
SELECT NVL(hire_date, 'Not hired') FROM employees;   -- DATE vs VARCHAR2

-- GOOD: align types explicitly
SELECT NVL(TO_CHAR(hire_date, 'YYYY-MM-DD'), 'Not hired') FROM employees;

-- GOOD: return a date sentinel value
SELECT NVL(hire_date, DATE '1900-01-01') FROM employees;

-- COALESCE requires all arguments to be compatible
SELECT COALESCE(start_date, end_date, DATE '2000-01-01')  -- All DATE: OK
FROM projects;

5. Fix Views After Schema Changes

-- After a column type change, find and recompile dependent views
SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID'
  AND object_type IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
ORDER BY object_type, object_name;

-- Recompile all invalid objects
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);

-- Alternatively, recompile a specific view
ALTER VIEW my_union_view COMPILE;

Prevention Strategies

1. Use Explicit CAST in All UNION Queries

-- Template for type-safe UNION queries
SELECT
  CAST(id         AS NUMBER)        AS id,
  CAST(name       AS VARCHAR2(200)) AS name,
  CAST(event_date AS DATE)          AS event_date,
  'SOURCE_A' AS source_label
FROM source_a
UNION ALL
SELECT
  CAST(ref_id     AS NUMBER),
  CAST(full_name  AS VARCHAR2(200)),
  CAST(created    AS DATE),
  'SOURCE_B'
FROM source_b;

2. Create Type-Consistent Views

-- Define a view that enforces types so consumers cannot mismatch
CREATE OR REPLACE VIEW v_unified_events AS
SELECT
  TO_CHAR(event_id)                      AS event_id,
  event_name,
  TO_CHAR(event_date, 'YYYY-MM-DD')      AS event_date,
  CAST(event_amount AS NUMBER(15,2))     AS event_amount
FROM table_a
UNION ALL
SELECT
  TO_CHAR(order_id),
  order_desc,
  TO_CHAR(order_date, 'YYYY-MM-DD'),
  CAST(order_total AS NUMBER(15,2))
FROM table_b;

3. Test UNION Branches in Isolation

• During development, always run each UNION branch as a standalone SELECT to confirm types
• Use DESCRIBE or USER_TAB_COLUMNS to compare column types before writing a UNION
• Add WHERE 1=0 to union branches in unit tests to validate structure without data

4. Code Review Standards

• Flag any UNION query that does not use explicit CAST or TO_CHAR/TO_DATE conversions
• Require schema change tickets to include a search for UNION queries referencing altered columns
• Use SQL linting tools that report implicit type conversion warnings

Related Errors

• ORA-00932 - Inconsistent datatypes
• ORA-01722 - Invalid number
• ORA-01830 - Date format picture ends before converting
• ORA-01861 - Literal does not match format string