Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How correctly merge tables? (without type data error)

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

  • marcothesanemarcothesane Employee
    Accepted Answer

    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
    );
    

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.