Error: ORA-01789: query block has incorrect number of result columns

sqlplus

Solution

Both views must have the same number of columns in order to perform a UNION ALL. Look at Oracle reference: The UNION ALL, INTERSECT, MINUS Operators

Problem

I have two relational views. First view: ``` CREATE VIEW VIEW1 AS SELECT T#, MAX(DECODE(LEG#,1,DEPARTURE)) ORIGIN, MAX(DECODE(LEG#,1,DESTINATION)) DESTINATION1 FROM TRIPLEG WHERE T# IN (SELECT T# FROM TRIPLEG WHERE LEG# < 3 AND T# IN (SELECT T# FROM TRIPLEG GROUP BY T# HAVING COUNT(T#) < 3) GROUP BY T#) GROUP BY T# ORDER BY T#; ``` Second view: ``` CREATE VIEW VIEW2 AS SELECT T#, MAX(DECODE(LEG#,2,DESTINATION)) DESTINATION1 FROM TRIPLEG WHERE T# IN (SELECT T# FROM TRIPLEG WHERE LEG# < 3 AND T# IN (SELECT T# FROM TRIPLEG GROUP BY T# HAVING COUNT(T#) < 3) GROUP BY T#) GROUP BY T# ORDER BY T#; ``` I use SELECT * FROM VIEW1 UNION ALL SELECT * FROM VIEW2 And I got the error ORA-01789. What I want to achieve is ``` T# | ORIGIN | DESTINATION1 | DESTINATION2 ------------------------------------------------------ 1 abc efg hij ``` is what I create considered relational views?

Original source