[The_Duck]

ARGH!
Why isn't this stupid MERGE command working???

The frickin column exists in the table.

SID:dev> MERGE INTO THERMOSTAT_EVENT_LOG
2 USING (SELECT *
3 FROM test_view@test) incoming_read
4 ON (thermostat_event_log.org_id = incoming_read.org_id and
5 thermostat_event_log.cust_id = incoming_read.cust_id and
6 thermostat_event_log.prem_seq_nbr = incoming_read.prem_seq_nbr and
7 thermostat_event_log.thrmstat_seq_nbr = incoming_read.thrmstat_seq_nbr and
8 thermostat_event_log.thrmstat_evt_type_cd = incoming_read.thrmstat_evt_type_cd and
9 thermostat_event_log.mesg_dt = incoming_read.mesg_dt and
10 thermostat_event_log.source_cd = incoming_read.source_cd
11 )
12 WHEN MATCHED THEN
13 UPDATE SET thermostat_event_log.org_id = incoming_read.org_id,
14 thermostat_event_log.cust_id = incoming_read.cust_id,
15 thermostat_event_log.prem_seq_nbr = incoming_read.prem_seq_nbr,
16 thermostat_event_log.thrmstat_seq_nbr = incoming_read.thrmstat_seq_nbr,
17 thermostat_event_log.thrmstat_evt_type_cd = incoming_read.thrmstat_evt_type_cd,
18 thermostat_event_log.mesg_dt = incoming_read.mesg_dt,
19 thermostat_event_log.source_cd = incoming_read.source_cd,
20 thermostat_event_log.evt_val_txt = incoming_read.evt_val_txt
21 WHEN NOT MATCHED THEN INSERT (thermostat_event_log.org_id, thermostat_event_log.cust_id, thermostat_event_log.prem_seq_nbr,
22 thermostat_event_log.thrmstat_seq_nbr, thermostat_event_log.thrmstat_evt_type_cd, thermostat_event_log.mesg_dt,
23 thermostat_event_log.source_cd,thermostat_event_lo g.evt_val_txt)
24 VALUES (incoming_read.org_id, incoming_read.cust_id, incoming_read.prem_seq_nbr,
25 incoming_read.thrmstat_seq_nbr, incoming_read.thrmstat_evt_type_cd, incoming_read.mesg_dt,
26 incoming_read.source_cd,incoming_read.evt_val_txt) ;
ON (thermostat_event_log.org_id = incoming_read.org_id and
*
ERROR at line 4:
ORA-00904: "THERMOSTAT_EVENT_LOG"."ORG_ID": invalid identifier

SID:dev> desc THERMOSTAT_EVENT_LOG
Name Null? Type
----------------------------------------- -------- ------------------
ORG_ID NOT NULL VARCHAR2(30)
CUST_ID NOT NULL VARCHAR2(15)
PREM_SEQ_NBR NOT NULL NUMBER(2)
THRMSTAT_SEQ_NBR NOT NULL NUMBER(2)
THRMSTAT_EVT_TYPE_CD NOT NULL VARCHAR2(3)
MESG_DT NOT NULL DATE
SOURCE_CD NOT NULL VARCHAR2(1)
EVT_VAL_TXT VARCHAR2(1000)

[lrobin3]

Not that it will make you feel any better, but I'm getting the same type of problem (Oracle 9i).

Layne

[The_Duck]

F- Merge

it can go take a flying leap.

[AlanP]

I too have had problems in using MERGE with either database links or select ... from dual. If MERGE works its great but it seems to fail in these two important areas.

Alan