|
Hi All,
We are encountering a strange problem with the merge command.
The following statement works :-
merge into ATTRIBUTE_GROUP@US_PRODUCT_UAT a
using
(
select
a1.group_id,
a1.NAME,
a1.CREATE_DATE,
a1.MODIFY_DATE
from
ATTRIBUTE_GROUP_LOG a1,
product_push_wrk a2
where
a2.column_id = a1.group_id and
a2.modify_date = a1.modify_date ) b
on ( a.group_id = b.group_id)
when matched then
update set
a.NAME = b.NAME,
a.CREATE_DATE = b.CREATE_DATE,
a.MODIFY_DATE = b.MODIFY_DATE
when not matched then
insert
(
a.group_id,
a.NAME,
a.CREATE_DATE,
a.MODIFY_DATE
)
values
(
b.group_id,
b.NAME,
b.CREATE_DATE,
b.MODIFY_DATE
);
However when we change the order of the columns in the select query as follows the an error occurs : -
merge into ATTRIBUTE_GROUP@US_PRODUCT_UAT a
using
(
select
a1.NAME,
a1.group_id,
a1.CREATE_DATE,
a1.MODIFY_DATE
from
ATTRIBUTE_GROUP_LOG a1,
product_push_wrk a2
where
a2.column_id = a1.group_id and
a2.modify_date = a1.modify_date ) b
on ( a.group_id = b.group_id)
when matched then
update set
a.NAME = b.NAME,
a.CREATE_DATE = b.CREATE_DATE,
a.MODIFY_DATE = b.MODIFY_DATE
when not matched then
insert
(
a.group_id,
a.NAME,
a.CREATE_DATE,
a.MODIFY_DATE
)
values
(
b.group_id,
b.NAME,
b.CREATE_DATE,
b.MODIFY_DATE
);
ERROR at line 15:
ORA-00904: "B"."GROUP_ID": invalid identifier
SQL> l 15
15* on ( a.group_id = b.group_id)
The structure of the attribute_log table is as follows :-
SQL> desc ATTRIBUTE_GROUP
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
GROUP_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(96)
CREATE_DATE NOT NULL DATE
MODIFY_DATE NOT NULL DATE
Any pointers to the cause of this error will be highly appreciated.
Thanks and Regards,
Suman
| 
