cdc

 select 

CONCAT(TRIM(ASSET.asset_number),0),asset.data_sou) AS cdc_key

,CONCAT(nvl(TRIM(asset.asset_number),'0'),asset.data_sou) AS dat_sou

,md5(concat(nvl(asset.asset_number,'0'),nvl(asset.dat_sou,'0'),nvl(asset.coln,'0') AS cdc_hash

,asset.*

from (

select distinct CONCAT(trim(cus_nu),'-'),

cast(NULL as string) as vendor_id,

ACUR.*,

'N' ISDELETED

from INERMIDTE.ACUR

)ASSET

=====================================

WITH ADDTION

AS (

SELECT A.* 

,current_timestamp AS Date_created1,

,current_timestamp AS Date_updated1,

,'N' AS ISDELTED1

FROM precdc.asset A

left outer join processed.asset B ON (A.cdc_key = B.cdc_key) 

WHERE B.data_sou='1002'

AND 

(B.cdc_hash IS Null)

)

,DELETION 

AS (

SELECT B.*

,B.current_timestamp AS Date_created1,

,B.current_timestamp AS Date_updated1,

,'Y' AS ISDELTED1

FROM precdc.asset A

rigt outer join processed.asset B ON (A.cdc_key = B.cdc_key) 

WHERE B.data_sou='1002'

AND  (B.cdc_hash IS Null) and isdeleted <> 'Y'

)

,UPDATION 

AS (

SELECT A.*

,B.current_timestamp AS Date_created1,

,A.current_timestamp AS Date_updated1,

,'N' AS ISDELTED1

FROM precdc.asset A

join processed.asset B ON (A.cdc_key = B.cdc_key) 

WHERE B.data_sou='1002'

AND  (B.cdc_hash <> B.cdc_hash)

)

select delta.cdc_key,

,delta.cdc_hash

,delta.*

)

FROM

SELECT * FROM ADDTION

UNION

SELECT * FROM DELETION

UNOIN

SELECT * FROM UPDATION

)delta