ORA-00904: error on merge statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-00904: error on merge statement

  1. #1
    Join Date
    Apr 2004
    Location
    ny,ny
    Posts
    24

    ORA-00904: error on merge statement

    merge into aff_trial_source_label a
    using (select name from affiliate_parent) p
    on (a.source=p.name)
    when matched then update set a.source=p.name
    when not matched then insert(a.source_label_id,a.source,a.source_label) values(aff_trial_source_seq.nextval,p.name,p.name)



    7 on (a.source=p.name)
    *
    ERROR at line 3:
    ORA-00904: "A"."SOURCE": invalid identifier

    the column name does exist in the table:

    SQL> desc aff_trial_source_label
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SOURCE_LABEL_ID NUMBER(13)
    SOURCE VARCHAR2(512)
    SOURCE_LABEL VARCHAR2(512)



    any insight would be helpful.
    thanks.

  2. #2
    Join Date
    Sep 2004
    Posts
    1
    you can't use a varchar for the join in the "ON" clause or it bombs. Create a numeric join field or join on a numeric and you'll be ok.

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    You can use a varchar in your join condition. Source is a reserved word.

    SQL> create table t1(id number, name varchar2(10), source varchar2(10));

    Table created.

    SQL>
    SQL> create table t2(id number, name varchar2(10), source varchar2(10));

    Table created.

    SQL>
    SQL> merge into t1 a
    2 using (select source, name from t2) b
    3 on (a.SOURCE = b.name)
    4 when matched then update set a.source = b.name
    5 when not matched then insert (source, name)
    6 values (b.name, b.source);
    on (a.SOURCE = b.name)
    *
    ERROR at line 3:
    ORA-00904: invalid column name


    SQL>
    SQL> merge into t1 a
    2 using (select source, name from t2) b
    3 on (a.name = b.name)
    4 when matched then update set a.source = b.name
    5 when not matched then insert (source, name)
    6 values (b.name, b.source);

    0 rows merged.

    SQL> select *
    2 from v$reserved_words
    3 where keyword = 'SOURCE';

    KEYWORD
    -------------------------------
    LENGTH
    ----------
    SOURCE
    6

Posting Permissions

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



Click Here to Expand Forum to Full Width