Creating View using UNION/UNION ALL

[Fiona]

Hi. I'm trying to create a view so I can put 4 tables into one view.
Here's an example of SQL I was trying out:

CREATE VIEW INFO AS
(SELECT * FROM TRANS_PER_MIN
UNION ALL
SELECT * FROM BUFFY)

Problem is, all four tables have different numbers of columns because of that I get error message:
ORA-01789: query block has incorrect number of result columns.

Have I missed something in the SQL, or should I be using other joins to create the view?

Thanks for the help
Fiona

[chrisrlong]

Well, since the view will look just like a table when done, you will only get one resultset with one set of columns. Therefore, if you want to add together several result sets into one, that's fine, but they must all have the same number of columns and datatypes. Furthermore, the names for the columns will come from the first selects.

Therefore, if you must 'fill in' the missing columns for each select, and name them all in the first select.

For example:

SELECT
--COL1,
--COL2,
--TO_CHAR(NULL) AS COL3,
--COL3 AS COL4
FROM
--T1
UNION
SELECT
--TO_DATE(NULL),
--COL1,
--COL2,
--COL3
FROM
--T2
...
etc.

Hope this helps,

- Chris