5 Replies Latest reply: Mar 23, 2013 5:18 AM by Mohamed ELAzab RSS

    Create Table Error

    user7202581
      Hi,
      I'm getting an error as follows .

      create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
      where
      a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
      and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
      and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
      and a.SRC_SYS_DESC = 'FACET');
      create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
      *
      ERROR at line 1:
      ORA-00957: duplicate column name

      Please advise

      Regards,
      Narayan
        • 1. Re: Create Table Error
          Justin_Mungal
          user7202581 wrote:
          Hi,
          I'm getting an error as follows .

          create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
          where
          a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
          and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
          and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
          and a.SRC_SYS_DESC = 'FACET');
          create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
          *
          ERROR at line 1:
          ORA-00957: duplicate column name

          Please advise

          Regards,
          Narayan
          I would imagine that a column of the same name exists in the tables used in your from clause. Instead of using +Select *+, specify the actual list of columns, and rename any duplicates (or exclude them if they aren't needed).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          • 2. Re: Create Table Error
            user7202581
            There are no duplicate columns.
            How can I create a table based on the condition mentioned in the SQL?

            Regards,
            Narayan
            • 3. Re: Create Table Error
              Jonathan Lewis
              user7202581 wrote:
              create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
              where
              a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
              and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')  
              and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
              and a.SRC_SYS_DESC = 'FACET');
              create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
              *
              ERROR at line 1:
              ORA-00957: duplicate column name
              That's because your definition includes duplicate column names - I can see them.
              Your select includes a.CNTCT_KEY, s.CNTCT_KEY, a.SCN_NUM, s.SCN_NUM.
              You'll have to list the columns you want, specicfying only one from each pair.

              Regards
              Jonathan Lewis
              • 4. Re: Create Table Error
                Justin_Mungal
                shakes head

                I should have read his where clause.
                • 5. Re: Create Table Error
                  Mohamed ELAzab
                  Hello,

                  you have to select one column only from the columns you are using in your definition.

                  Kind Regards
                  Mohamed ElAzab
                  http://mohamedelazab.blogspot.com

                  Edited by: Mohamed ELAzab on Mar 23, 2013 12:17 PM