Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: : invalid identifier during merge
ORA-00904: : invalid identifier during merge [message #429589] Wed, 04 November 2009 12:31 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
There are 2 tables, testlog and testlog2.
testlog2 is a log table used to record all the old values, new
values for columns of testlog

create table testlog(lnum number, col1 number, col2 number, col3 number)
/
insert into testlog values (1234,1,1,1)
/
insert into testlog values (1233,2,8,1)
/
insert into testlog values (5233,2,8,4)
/

create table testlog2(lnum number,type varchar2(20),pval number,nval number)




how is testlog2 populated ? based on below proc and trigger


CREATE OR REPLACE PROCEDURE pt_log (
inlnum IN number
, intype IN varchar2
, inpval IN number
, innval IN number
)
IS

--

BEGIN



--
IF inpval != innval
THEN
INSERT INTO testlog2
(lnum,type,pval,nval
)
VALUES (inlnum , intype, inpval, innval
);
END IF;

EXCEPTION
WHEN OTHERS
THEN


RAISE;
END pt_log;
/



CREATE OR REPLACE TRIGGER trig_testlog BEFORE UPDATE ON testlog
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
If :new.ctr is null then
:new.ctr:=1;
else


if :old.col1 :new.col1 or :old.col2 :new.col2 or :old.col3 :new.col3 then
:new.ctr:=:old.ctr+1;
end if;


end if;


IF UPDATING ('COL1')
THEN
pt_log (:OLD.lnum
, 'COL1'
, :OLD.COL1
, :NEW.COL1
);
END IF;



IF UPDATING ('COL2')
THEN
pt_log (:OLD.lnum
, 'COL2'
, :OLD.COL2
, :NEW.COL2
);
END IF;


IF UPDATING ('COL3')
THEN
pt_log (:OLD.lnum
, 'COL3'
, :OLD.COL3
, :NEW.COL3
);
END IF;


End;
/






now i am adding a new column, ctr to testlog, the purpose of this
column is to hold the count of number of times each lnum is
modified...

alter table testlog add (ctr number)
/

now for future, i updated the above trigger, to populate ctr
column for each row...

but i am thinking about how to populate this for the records
already stored in testlog2..

so i tried this

merge into testlog  src
using (SELECT distinct COUNT(*)
OVER(PARTITION BY lnum ) NUM_RECS
FROM testlog2  
) dat
on (src.lnum = dat.lnum )
when matched then update set ctr = NUM_RECS


I get this error

ORA-00904: "DAT"."LNUM": invalid identifier
Re: ORA-00904: : invalid identifier during merge [message #429590 is a reply to message #429589] Wed, 04 November 2009 12:43 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
merge into testlog  src
using (SELECT distinct COUNT(*)
OVER(PARTITION BY lnum ) NUM_RECS
FROM testlog2  
) dat
on (src.lnum = dat.lnum )
when matched then update set ctr = NUM_RECS



Have a look at
src.lnum = dat.lnum


lnum column is not part of the select below
SELECT distinct COUNT(*)
OVER(PARTITION BY lnum ) NUM_RECS
FROM testlog2 
Re: ORA-00904: : invalid identifier during merge [message #429594 is a reply to message #429589] Wed, 04 November 2009 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition , the code you posted does not compile and you still have the stupid "when others".

Regards
Michel
Re: ORA-00904: : invalid identifier during merge [message #429595 is a reply to message #429594] Wed, 04 November 2009 12:55 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Correct, seems that there should be a separate sticky (Exception handling) here on this.The same thing is being repeated several times here in this forum


@ora1980
You should read the below links (may be helpful for you)

http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

[Updated on: Wed, 04 November 2009 13:03]

Report message to a moderator

Re: ORA-00904: : invalid identifier during merge [message #429599 is a reply to message #429589] Wed, 04 November 2009 13:08 Go to previous message
ora1980
Messages: 251
Registered: May 2008
Senior Member
thanks
Previous Topic: Autonomous Transaction in a Trigger
Next Topic: help me on CURSOR
Goto Forum:
  


Current Time: Sat Dec 03 12:28:27 CST 2016

Total time taken to generate the page: 0.15583 seconds