topic Re: Hash Generation not working on CLOB Column in Qlik Replicate

Hash Generation not working on CLOB Column

Qliksurf — Fri, 26 Jul 2024 12:44:22 GMT

While reading data from Oracle and writing to snowflake, We are using hash_sha256 to generate hash on set of columns, We use this hash value as a key.

Issue: Hash value is not generated when we use a column that has CLOB Data type.

Is there any limitation to hash_sha256 ?

Re: Hash Generation not working on CLOB Column

john_wang — Fri, 26 Jul 2024 13:07:03 GMT

Hello @Qliksurf ,

Welcome to Qlik Community forum and thanks for reaching out here!

Any transformation on LOB columns will get NULL result. This is the default behavior. See User Guide limitation:

The only supported transformation for LOB/CLOB data types is to drop the column on the target.

Hope this helps.

John.

Re: Hash Generation not working on CLOB Column

Geetha — Fri, 26 Jul 2024 13:08:05 GMT

Hi @Qliksurf ,

In addition to the initial response you can check the following community discussion which explains a workaround: https://community.qlik.com/t5/Qlik-Replicate/String-truncation-during-transformation/m-p/1977716/highlight/true#M3656.

The suggestion is to use a source_lookup function to first convert LOB --> string and then apply the desired transformation.

This appears to be working for a customer, Kindly check and confirm.

Best Regards,
Geetha

Re: Hash Generation not working on CLOB Column

Heinvandenheuvel — Fri, 26 Jul 2024 13:22:53 GMT

Untested...

On of the few ransformation functions taking a LOB as input is HEX(x).

Maybe you can use hash_sha256(hex($x)) ? Toss in a substr if need be.

Hein.