1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Merge stament error

Discussion in 'SQL PL/SQL' started by sowmyav, Dec 11, 2008.

  1. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,
    Please find the below query.Could anyone pls help me on this query.

    Code (Text):

    SQL>   merge into sup1 a
      2    using sup2 b
      3    on
      4    (a.ASUP_ID=b.ASUP_ID and a.EVT_GMT_SEC= b.EVT_GMT_SEC and a.SYS_SERIALNO=b.SYS_SERIALNO and
      5     a.SYS_MODEL=b.SYS_MODEL and a.SYS_TYPE=b.SYS_TYPE and a.DVC_SERIALNO=b.DVC_SERIALNO and a.DVC_MODEL=b.DVC_MODEL)
      6   when matched then
      7    update set
      8    a.sys_version = b.sys_version,a.sys_top_domain = b.sys_top_domain,
      9    a.DVC_SIZE_GB =b.DVC_SIZE_GB,a.DVC_FW_REV = b.DVC_FW_REV,a.ASUP_LINK =b.ASUP_LINK,
     10    a.S_ISENSEKEY = b.S_ISENSEKEY,a.S_IASC =a.S_IASC,a.S_IASCQ =b.S_IASCQ,a.S_IFRU =b.S_IFRU
     11    when not matched then
     12    insert values (b.ASUP_ID,b.EVT_GMT_SEC,b.SYS_SERIALNO,b.SYS_MODEL,
     13    b.SYS_VERSION,b.SYS_TOP_DOMIAN,b.SYS_TYPE,
     14    b.DVC_SERIALNO,b.DVC_MODELb.DVC_SIZE_Ga,
     15    b.DVC_FW_REV,b.ASUP_LINK,b.S_ISENSEKEY,b.S_IASC,b.S_IASCQ,b.S_IFRU);
      merge into sup1 a
                 *
    ERROR at line 1:
    ORA-00947: not enough values
     
    Note:
    The count(*) values for sup1 is 30 and sup2 is 60.I want to merge.
    .

    Thanks in adv,
    Sowmya
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Check how many columns sup1 is having and how many is populated in the merge statement. both are supposed to be the same.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Smells some thing fishy in 14th line .

     
  4. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thanks a lot Raj! for your timely response.

    Could you please help on the same as this is first merge program and I tried solving it, but coudn't find out the error.

    But I got the error even though I changed it.

    ~~~~~~~~~~~~~~~~~Entire screen shot for your reference~~~~~~~
    Code (Text):

    SQL> select count(*) from sup1;

      COUNT(*)
    ----------
             2

    SQL> select count(*) from sup2;

      COUNT(*)
    ----------
             3

    SQL> desc sup1
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------
     ASUP_ID                                   NOT NULL CHAR(15)
     EVT_GMT_SEC                               NOT NULL NUMBER(15)
     SYS_SERIALNO                              NOT NULL VARCHAR2(32)
     SYS_MODEL                                 NOT NULL VARCHAR2(16)
     SYS_VERSION                                        VARCHAR2(128)
     SYS_TOP_DOMAIN                                     VARCHAR2(32)
     SYS_TYPE                                  NOT NULL VARCHAR2(16)
     DVC_SERIALNO                              NOT NULL VARCHAR2(32)
     DVC_MODEL                                 NOT NULL VARCHAR2(24)
     DVC_SIZE_GB                                        NUMBER(8)
     DVC_FW_REV                                         VARCHAR2(32)
     ASUP_LINK                                          VARCHAR2(4000)
     S_ISENSEKEY                                        VARCHAR2(512)
     S_IASC                                             VARCHAR2(512)
     S_IASCQ                                            VARCHAR2(512)
     S_IFRU                                             VARCHAR2(512)




    SQL> merge into sup1 a
      2  using sup2 b
      3  on
      4  (a.ASUP_ID=b.ASUP_ID and
      5   a.EVT_GMT_SEC= b.EVT_GMT_SEC and a.SYS_SERIALNO=b.SYS_SERIALNO and
      6  a.SYS_MODEL=b.SYS_MODEL and a.SYS_TYPE=b.SYS_TYPE and a.DVC_SERIALNO=b.DVC_SERIALNO and a.DVC_MODEL=b.DVC_MODEL)
      7  when matched then
      8  update set
      9  a.sys_version = b.sys_version,
     10  a.sys_top_domain = b.sys_top_domain,
     11  a.DVC_SIZE_GB =b.DVC_SIZE_GB,
     12  a.DVC_FW_REV = b.DVC_FW_REV,
     13  a.ASUP_LINK =b.ASUP_LINK,
     14  a.S_ISENSEKEY = b.S_ISENSEKEY,
     15  a.S_IASC =a.S_IASC,
     16  a.S_IASCQ =b.S_IASCQ,
     17  a.S_IFRU =b.S_IFRU
     18   when not matched then
     19  insert values (
     20  b.SYS_VERSION,
     21  b.SYS_TOP_DOMIAN,
     22  b.DVC_SIZE_GB,
     23  b.DVC_FW_REV,
     24  b.ASUP_LINK,
     25  b.S_ISENSEKEY,
     26  b.S_IASC,
     27  b.S_IASCQ,
     28  b.S_IFRU);
    merge into sup1 a
               *
    ERROR at line 1:
    ORA-00947: not enough values


    SQL>
     
  5. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Raj, It worked!

    3 rows merged.

    Thank u so much!
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    SUP1 is having 16 columns and you are inserting only 9 column values.

    You either have to populate all columns Or need to specify the column list before values clause.