Home » SQL & PL/SQL » SQL & PL/SQL » merge invalid identifier
merge invalid identifier [message #189499] Thu, 24 August 2006 22:09 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
why wont this work?


CTS@ctstestnew:SQL>desc tlog
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------

 BEGIN                                              DATE
 END                                                DATE


CTS@ctstestnew:SQL> merge into tlog
  2   using mlog
  3   on (mlog.begin = tlog.begin)
  4   when matched then update set tlog.begin = mlog.begin,
  5    tlog.end = mlog.end
  6   when not matched then insert values(mlog.begin, mlog.end)
  7  /
 on (mlog.begin = tlog.begin)
                  *
ERROR at line 3:
ORA-00904: "TLOG"."BEGIN": invalid identifier



Re: merge invalid identifier [message #189535 is a reply to message #189499] Fri, 25 August 2006 01:03 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

are u sure there's a field named begin in table tlog.please check.coz the code works for me.and avoid using the reserved words begin and end.



regards,
Re: merge invalid identifier [message #189547 is a reply to message #189535] Fri, 25 August 2006 01:31 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
yep, i've also posted the description of the table tlog. ive change the column names but still an error:


CTS@ctstestnew:SQL>ed
Wrote file afiedt.buf

  1  merge into tlog
  2  using mlog
  3  on (mlog.be = tlog.be)
  4  when matched then update set tlog.be = mlog.be,
  5  tlog.en = mlog.en
  6* when not matched then insert values(mlog.be, mlog.en)
CTS@ctstestnew:SQL>/
on (mlog.be = tlog.be)
              *
ERROR at line 3:
ORA-00904: "TLOG"."BE": invalid identifier


CTS@ctstestnew:SQL>desc tlog
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------

 BE                                                 DATE
 EN                                                 DATE

CTS@ctstestnew:SQL>desc mlog
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------

 BE                                                 DATE
 EN                                                 DATE

CTS@ctstestnew:SQL>

[Updated on: Fri, 25 August 2006 01:35]

Report message to a moderator

Re: merge invalid identifier [message #189556 is a reply to message #189499] Fri, 25 August 2006 01:49 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi

Your query

merge into tlog
using mlog
on (mlog.be = tlog.be)
when matched then update set tlog.be = mlog.be,
tlog.en = mlog.en
when not matched then insert values(mlog.be, mlog.en)


Try this query

MERGE INTO TLOG
USING MLOG
ON (TLOG.BEGIN_D = MLOG.BEGIN_D)
WHEN matched THEN UPDATE SET TLOG.END_D = MLOG.END_D
WHEN NOT matched THEN INSERT VALUES(MLOG.BEGIN_D, MLOG.END_D);

You can't update the column you are validating. You are validating on TLOG.BEGIN and updating the same column which is not possible.

Thks & Rgds
venkat
Re: merge invalid identifier [message #189560 is a reply to message #189556] Fri, 25 August 2006 01:55 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thank you so much sir, and to sir rollie, that solved it =)
Previous Topic: string problem
Next Topic: Problem with cost based optimizer?
Goto Forum:
  


Current Time: Thu Dec 08 18:19:11 CST 2016

Total time taken to generate the page: 0.08178 seconds