CODEWITHSUNDEEP
hana
jahan
jahan

Reputation: 153

Why incremental load inserts duplicate records to HANA?

My query looks like this:

INSERT INTO TARGET TABLE
 (
      id
     ,order_no
     ,event_ts
     ,rcrd_cre_ts
 )
 SELECT
       src.id
      ,src.order_no
      ,src.event_ts
      ,UTCTOLOCAL(CURRENT_TIMESTAMP,'PST') 
  FROM    
  (
    SELECT
         CAST(id AS VARCHAR(40)) AS id
        ,event_ts
        ,CAST(order_no AS VARCHAR(20)) AS order_no
    FROM
     STAGE TABLE
    WHERE order_no <> ''
      AND order_no IS NOT NULL
   ) src
   LEFT JOIN TARGET TABLE
     ON (src.id = tgt.id AND src.order_no = tgt.order_no AND src.event_ts = tgt.event_ts )
   AND tgt.order_no IS NULL
 ;

I'm already casting event_ts as TIMESTAMP in the stage table itself. Let's say I already have a record 1 like this in the target table, if I run the same job again, it is inserting a duplicate record into the target table with the below id, order and event_ts key combination. My assumption is something got wrong around event_ts. though it's showing yyyy-mm-dd hh:mm:ss, it is storing something else. Can someone help where am I going wrong?

EX45ERC657 2021-07-02 13:31:02  25 2021-07-02 14:46:11
EX45ERC657 2021-07-02 13:31:02  25 2021-07-02 14:52:11

event_ts from source is coming as example:

2021-07-02T13:31:02.209Z

Upvotes: 0

Views: 301

Answers (1)

jahan
jahan

Reputation: 153

I got the issue. Instead of

LEFT JOIN TARGET TABLE
     ON (src.id = tgt.id AND src.order_no = tgt.order_no AND src.event_ts = tgt.event_ts )
   AND tgt.order_no IS NULL

it has to be

LEFT JOIN TARGET TABLE
     ON (src.id = tgt.id AND src.order_no = tgt.order_no AND src.event_ts = tgt.event_ts )
   where tgt.order_no IS NULL

Sorry, but it seem to be working.

Upvotes: 1

Related Questions