oracle-databaseoracle11gcreate-view

Oracle - how to perform left join on views?


How to create a views using left join on Oracle?

I am be able to create a join table using this statement

select *
from
    concert_copy c
    left join event_copy e
        on c.concert_id=e.concert_id;

But when I create views using this statement gave me error

"ORA-00957: duplicate column name"

create view concert_event as
    select *
    from
        concert_copy c
        left join event_copy e
            on c.concert_id=e.concert_id;

Solution

  • At least the concert_id column appears in both tables. SELECT * would result in a view having 2 columns with the same name. Resolution: specify the fields explicitly at least for one of the tables

    create view concert_event as
        select c.*, e.eventname, ...
        from
            concert_copy c
            left join event_copy e
                on c.concert_id = e.concert_id;
    

    If there are other duplicate names, use aliases. E.g. If both tables have a column name

    select c.*, e.name as eventname, ...
    

    Note: if you call SELECT directly, Oracle automatically creates generic names for duplicate columns and expressions, but in views it is required that all column names are visible from the declaration, either because they match the name of a selected column or because an alias is given.