Problems with merge script

From: ktayloraz <ktaylor_at_getnet.net>
Date: Thu, 9 Jun 2005 14:13:49 -0700
Message-ID: <20050609140942.G54101@dhcpdns2.ddsoho.com>

Ever had one of those days?

I simple merge script, to take data from a temp table, and load it into a live table. Sounds easy, right?

Well, the error comes back as:

SQL> @mrg_advisor_dim.sql
on (trg.adv_dsi_id = src.adv_dsi_id )

    *
ERROR at line 3:
ORA-00904: "TRG"."ADV_DSI_ID": invalid identifier

and the code looks like such:

---

merge into t_advisor_dim trg
using temp_advisor_dim src
on (trg.adv_dsi_id = src.adv_dsi_id )
when matched then update set

trg.ADV_DSI_ID = src.ADV_DSI_ID,
trg.ADV_NBR = src.ADV_NBR,
trg.ADV_FIRST_NME = src.ADV_FIRST_NME,
trg.ADV_LAST_NME = src.ADV_LAST_NME,
trg.ADV_DOB_DT = src.ADV_DOB_DT,
trg.ADV_SSN_NBR = src.ADV_SSN_NBR,
trg.EFF_APPT_DT = src.EFF_APPT_DT,
trg.EFF_TERM_DT = src.EFF_TERM_DT,
trg.HIRE_DT = src.HIRE_DT,
trg.SVP_IND = src.SVP_IND,
trg.GVP_IND = src.GVP_IND,
trg.FVP_IND = src.FVP_IND,
trg.ADV_TYPE_IND = src.ADV_TYPE_IND,
trg.POST_DTS = src.POST_DTS

when not matched then insert (

trg.ADV_DSI_ID,
trg.ADV_NBR,
trg.ADV_FIRST_NME,
trg.ADV_LAST_NME,
trg.ADV_DOB_DT,
trg.ADV_SSN_NBR,
trg.EFF_APPT_DT,
trg.EFF_TERM_DT,
trg.HIRE_DT,
trg.SVP_IND,
trg.GVP_IND,
trg.FVP_IND,
trg.ADV_TYPE_IND,
trg.POST_DTS)

values (

src.ADV_DSI_ID,
src.ADV_NBR,
src.ADV_FIRST_NME,
src.ADV_LAST_NME,
src.ADV_DOB_DT,
src.ADV_SSN_NBR,
src.EFF_APPT_DT,
src.EFF_TERM_DT,
src.HIRE_DT,
src.SVP_IND,
src.GVP_IND,
src.FVP_IND,
src.ADV_TYPE_IND,
src.POST_DTS);
_________________________________________________________

Naturally, I checked both tables, and adv_dsi_id is in both, and populated.

Can some one shead some light on this? Please?

TIA Karl E. Taylor
UNIX systems administrator Received on Thu Jun 09 2005 - 16:13:49 CDT