Skip to main content

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!

Transformation: Source Lookup - CLOB datatype - limitations on string functions

cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Litz
Support
Support

Transformation: Source Lookup - CLOB datatype - limitations on string functions

Environment

  • Replicate
  • Table/Field level transformation

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Field Transformation

CLOB data types have a limitation in that no string functions can be performed on them in a field transformation. There will be no errors in the log file, just that the field will be NULL.

On my Source  table from SQL, I am using the source lookup function under Data Enrichment in order to retrieve the CLOB field value and then apply a string function (replaceChars) to it. 

By using the source lookup function on the filed it is returned to the replicate task in a form where a string function can be applied.

This source lookup function is documented in the replicate user guide and the on-line help.

The general form of the function is as follows:

source_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)


For this example I am doing the lookup into the MyClob table:

source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID)


I will then wrap this lookup in a replacechars function:

ReplaceChars(source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID),'L','@')


NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
DBOis the schema in oracle
MyCLOB is the table
MyClob is the field that I want returned
MyID=? is the predicate for the lookup 
$MyID is the value from the field that we are using in the predicate in place of =?

NOTE: That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria. 

I will show a series of screen shots below that demonstrate the results of this exercise for the target table:

 Michael_Litz_0-1664503190090.png

Source DDL and data for MyClob table:

Michael_Litz_1-1664503298187.png

Michael_Litz_2-1664503352643.png

Target data for MyClob table (NO Transformations):

Michael_Litz_3-1664503437226.png

Target data for MyClob table (ReplaceChars() function on MyString and MyClob fields):

NOTE: There are no errors in the task log - just null values in the field.

MyString field transformation: replaceChars($MyClob,'M','@')

MyCLOB field transformation: replaceChars($MyClob,'L','@')

Michael_Litz_4-1664503558715.png

Target data for MyClob table (ReplaceChars(SourceLookup) function on MyClob fields):

NOTE: You will see that the replaceChars function worked.

MyString field transformation: replaceChars($MyClob,'M','@')

MyCLOB field transformation: ReplaceChars(source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID),'L','@')

Michael_Litz_5-1664503639077.png

Please feel free to try other string functions on the Clob field after once the source lookup returns the value. They should all work.

 

Version history
Last update:
‎2022-09-30 01:12 PM
Updated by:
Contributors