How correctly merge tables? (without type data error)

vlado1212

I try to merge two tables:
MERGE INTO event_log_target t USING event_log_candidate sc
ON t.event_uuid = sc.event_uuid
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ... VALUES(..)

I always have error like this: [Vertica]VJDBC ERROR: Column "event_value" is of type numeric but expression is of type varchar

But in the both tables i have type of this column: numeric(18,3)
What is the problem? How merge table?
Vertica version 9.0

marcothesane

Very probably, you have a messed up order in the WHEN MATCHED THEN UPDATE or the WHEN NOT MATCHED THEN INSERT branches of the statement - or both. It does not happen by name, but by order of columns.
The best way I have experienced to remain the master and not the victim of what happens to me in a MERGE statement, is to micro-manage it, writing each and every source-target match manually, like in the script below. Nothing is left to chance, if you do it like that:

MERGE /*+DIRECT*/
INTO scd.dim_customer_scd t
USING stg_scd.dim_customer_scd s
  ON t.cust_key = s.cust_key
WHEN MATCHED THEN UPDATE SET
  cust_key     = s.cust_key
, cust_id      = s.cust_id
, cust_from_dt = s.cust_from_dt
, cust_to_dt   = s.cust_to_dt
, cust_is_curr = s.cust_is_curr
, cust_cre_ts  = s.cust_cre_ts
, cust_udt_ts  = s.cust_udt_ts
, cust_fname   = s.cust_fname
, cust_lname   = s.cust_lname
, cust_phoneno = s.cust_phoneno
, cust_loy_lvl = s.cust_loy_lvl
, cust_org_id  = s.cust_org_id
WHEN NOT MATCHED THEN INSERT (
  cust_key
, cust_id
, cust_from_dt
, cust_to_dt
, cust_is_curr
, cust_cre_ts
, cust_udt_ts
, cust_fname
, cust_lname
, cust_phoneno
, cust_loy_lvl
, cust_org_id
) VALUES (
  s.cust_key
, s.cust_id
, s.cust_from_dt
, s.cust_to_dt
, s.cust_is_curr
, s.cust_cre_ts
, s.cust_udt_ts
, s.cust_fname
, s.cust_lname
, s.cust_phoneno
, s.cust_loy_lvl
, s.cust_org_id
);

vlado1212

Yes!
it works
very thank you