Results 1 to 4 of 4

Thread: Ora-00957

  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Question Unanswered: Ora-00957

    Hi,

    please find the error thrown here when i try creating a view..



    Code:
    SQL> create view zigview as select t.col1, z.col1 from testtab t, zigzag z;
    create view zigview as select t.col1, z.col1 from testtab t, zigzag z
                                            *
    ERROR at line 1:
    ORA-00957: duplicate column name
    My Oracle version : 8.1

    Does the lower versions create views without throwing errors? If so let me know the version upto which this type of statement is supported.

    Regards,
    Sn

  2. #2
    Join Date
    Apr 2005
    Posts
    127
    Also some one let me know is there any possibilities to make it work...

    cheers
    Sn

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You cannot define 2 columns with the same name (col1) in a view - for any Oracle version. This makes perfect sense - how would you select one of them?

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    ... So use aliases :

    Code:
    create view zigview 
    as select t.col1 as tcol1, z.col1 as zcol1 
    from testtab t, zigzag z;
    Code:
    rbaraer@Ora10g> create table testtab (col1 number);
    
    Table created.
    
    rbaraer@Ora10g> create table zigzag (col1 number);
    
    Table created.
    
    rbaraer@Ora10g> create view zigview
    as select t.col1 as tcol1, z.col1 as zcol1
    from testtab t, zigzag z;  2    3
    
    View created.
    
    rbaraer@Ora10g> desc zigview
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TCOL1                                                                            NUMBER
     ZCOL1                                                                            NUMBER
    
    rbaraer@Ora10g>
    The "as" keyword is not mandatory, you can also use :

    Code:
    create view zigview 
    as select t.col1 tcol1, z.col1 zcol1 
    from testtab t, zigzag z;
    Code:
    rbaraer@Ora10g> create or replace view zigview
    as select t.col1 tcol1, z.col1 zcol1
    from testtab t, zigzag z;  2    3
    
    View created.
    
    rbaraer@Ora10g> desc zigview
     Name                                                                    Null?    Type
     ----------------------------------------------------------------------- -------- -------------------------------------------------
     TCOL1                                                                            NUMBER
     ZCOL1                                                                            NUMBER
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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