ORA-01789 means the queries joined by a set operator, such as UNION or MINUS, return different numbers of columns. Rows can only be stacked when every query has the same number of columns. Add the missing columns, often as NULL placeholders, or remove the extra ones.
Reproduce the Error
Example:
select 1, 2 from dual union select 3 from dual;
Output:
Error starting at line : 1 In command - select 1, 2 from dual union select 3 from dual Error at Command Line : 1 Column : 1 Error report - SQL Error: ORA-01789: query block has incorrect number of result columns
Fix: Same Number of Columns
Example:
select 1 as a, 2 as b from dual union select 3, null from dual;
Output:
A B ____ ____ 1 2 3
NULL fills the second column for the row that has no value for it.
Things to Know
- SELECT * in one branch is fragile: adding a column to its table breaks the set operation. List the columns.
- Name the columns in the first query; the result uses those names.
- Columns with the same position but different types raise ORA-01790.
Related Guides
- How to Fix ORA-01790: Expression Must Have Same Datatype as Corresponding Expression in Oracle
- How to Use Set Operators in Oracle SQL
Conclusion
ORA-01789 means the branches of a set operation have different numbers of columns. List the columns explicitly in every branch and fill gaps with NULL so all branches line up.
