How correctly merge tables? (without type data error)
vlado1212
Community Edition User
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
Best Answer
-
marcothesane
- Select Field - Administrator
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 );
1
Answers
Yes!
it works
very thank you