Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Thumbs down Unanswered: MERGE command is not working

    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)

  2. #2
    Join Date
    May 2003
    Posts
    5

    Merge sucks

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

    Layne

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    F- Merge

    it can go take a flying leap.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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

Posting Permissions

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