2

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;
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
flyingduck92
  • 1,449
  • 2
  • 23
  • 39

2 Answers2

9

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.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
2

Don't use asterisk (i.e. SELECT *), but name ALL columns you select, giving each of them distinctive name. For example, if both CONCERT_COPY and EVENT_COPY have column named ID, you'd have to 

select c.id concert_id, e.id event_id, ...
from concert_copy c left join event_copy e ...
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • oh I can not use asterisk? Dang, i dont know that. Thx @Littlefoot – flyingduck92 Dec 30 '17 at 16:30
  • 2
    You can, but that's USUALLY a bad idea. It is better to name all columns, especially if you write code that will be used in production. Someone, who might need to maintain your code, would know exactly what you selected. "SELECT *" is OK for *quick and dirty* selects, but - as you can see - it can cause problems. – Littlefoot Dec 30 '17 at 16:35
  • is it possible do update on those column on view? why I can not update each column on the view? – flyingduck92 Dec 30 '17 at 18:16
  • 1
    Update? Yes, as well as insert or delete. Usual way to do that is via **instead of** trigger(s). More info here: https://docs.oracle.com/cd/B10501_01/server.920/a96524/c18trigs.htm#10901 – Littlefoot Dec 30 '17 at 19:26
  • Also see the answers to [In Oracle, is it possible to INSERT or UPDATE a record through a view?](https://stackoverflow.com/questions/1652995/in-oracle-is-it-possible-to-insert-or-update-a-record-through-a-view) – William Robertson Jan 01 '18 at 13:53