Creating View using UNION/UNION ALL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Creating View using UNION/UNION ALL

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    Question

    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
    Share on Google+

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width