CODEWITHSUNDEEP
upsolversqlake
Ajay C
Ajay C

Reputation: 66

Using MD5 and missing some records in my output in Upsolver SQLake

I'm losing some data in my output and need help to identify the issue. We create a hashkey using the below 3 columns, rest is pretty much straight forward select from data source Upsert on hashkey.

SET hashkey = MD5(advertiser_id || marketplace_id || retailer);

SELECT hashkey,
       col1, col2, col3...
REPLACE ON DUPLICATE hashkey

Upvotes: 0

Views: 67

Answers (1)

Ajay C
Ajay C

Reputation: 66

MD5 returns null if any of the inputs is null. Most likely this is causing the hashkey to return NULL if any of your three columns are null and then those records might be getting missed out. I would COALESCE the column which might be nullable to some fixed value like NA or anything to avoid the null case. For example, if marketplace_id can be null, then I would do below to solve this issue.

SET hashkey = MD5(advertiser_id || COALESCE(marketplace_id,'NA') || retailer);

Upvotes: 0

Related Questions