Hash Generation not working on CLOB Column

[Qliksurf]

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 ?

[john_wang]

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.

[Geetha]

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/hig...

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

[Heinvandenheuvel]

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.