Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Concatenate 3 tables into a view.

712537Jul 17 2009 — edited Jul 20 2009
Hello,
I'm relatively new to SQL as it has been many years since taking the PL/SQL course at Oracle. Using Oracle 9i.

I am attempting to build a view that is simply a concatenation of three tables. I guess the best way to explain is an example.
Lets say I have three tables, one for each of three cities with employee names in them.
i.e.
Table Ottawa Table Halifax Table Edmonton
first last first last first last
john smith john denver Tom Cochrane
jane doe rod stewart Van Halen

I want my view to look like (order of names doesn't matter):

first last
john smith
jane doe
john denver
rod stewart
Tom Cochrane
Van Halen

I tried
CREATE or UPDATE VIEW emp AS
(SELECT first, last FROM ottawa;
SELECT first, last FROM halifax;
SELECT first, last FROM edmonton)

But with no luck. I know that multiple SELECT statements work with a start file, but that doesn't seem to do me any good in this situation. Any help on this would be appreciated.

Thank you
This post has been answered by SanjayRs on Jul 17 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 17 2009
Added on Jul 17 2009
5 comments
794 views