https://community.qlik.com/t5/Official-Support-Articles/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170 <H4>Environment</H4> <UL> <LI>Replicate</LI> <LI>Table/Field level transformation</LI> </UL> <P style="font-style: italic;">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.</P> <H3>Field Transformation</H3> <P>On my Source &nbsp;table from Oracle, I am using the source lookup function under Data Enrichment in order to retrieve the Oracle ROWID field value.&nbsp; Not all fields are logged by oracle into the Redo Logs. In the case of ROWID we will use a source lookup function to query the source table and retrieve the field value, putting it&nbsp; into the Add Column that we defined and called ROWID. This source lookup function is documented in the replicate user guide and the on-line help.</P> <H4>The general form of the function is as follows:</H4> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%">source_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)</TD> </TR> </TBODY> </TABLE> <H4><BR />For this example in am doing the lookup into the Employees table:</H4> <TABLE border="1" width="100%"> <TBODY> <TR> <TD width="100%">source_lookup('<SPAN>NO_CACHING</SPAN>','HR','EMPLOYEES','ROWID','EMPLOYEE_ID=:1',$EMPLOYEE_ID)</TD> </TR> </TBODY> </TABLE> <P><BR />NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values<BR />HR is the schema in oracle<BR />EMPLOYEES is the table<BR />ROWID is what I want returned<BR />EMPLOYEE_ID=:1&nbsp;is the predicate for the lookup&nbsp;<BR />$EMPLOYEE_ID is the value from the redo log that we are using in the predicate in place of :1</P> <P>NOTE: If the source lookup needed more than one field in the key then:&nbsp;<BR />(Multiple variables would look like: 'EMPLOYEE_ID=:1 AND DEPARTMENT_ID=:2) <BR />$EMPLOYEE_ID, $DEPARTMENT_ID (in this example we are using field values that the task has read from the redo log file.</P> <P><STRONG>NOTE:</STRONG> That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria. Please also note that the Oracle ROWID is not guaranteed to persist. Traditional Oracle ROWID's can change&nbsp;if a table is quiesced or rebuilt with dbms_redefinition.</P> <P>This expression is created for the Add Column&nbsp;transformation ROWID as seen in the screen shot below.</P> <DIV id="tinyMceEditorMichael_Litz_0" class="mceNonEditable lia-copypaste-placeholder">&nbsp;</DIV> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Transformation Screen shot" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/54255i0BCEC3068F5EED2B/image-size/large?v=v2&amp;px=999" role="button" title="ROWID.jpg" alt="Transformation Screen shot" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Transformation Screen shot</span></span></P> <P>&nbsp;</P> <P><STRONG>NOTE:</STRONG> Unfortunately data enrichment function can not be tested on the screen, they must be saved and the task run for the transformation to be tested.</P> <P>The screen shot below shows the target table with the ROWID field after the task has run.</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Target Table with ROWID field" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/54256i41F1ABDA80909CAD/image-size/large?v=v2&amp;px=999" role="button" title="Oracle Rowid Target Table.png" alt="Target Table with ROWID field" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Target Table with ROWID field</span></span></P> <P>&nbsp;</P> <H4>Related Content</H4> <P>&nbsp;</P> Tue, 04 Jun 2024 12:52:04 GMT Michael_Litz 2024-06-04T12:52:04Z https://community.qlik.com/t5/Official-Support-Articles/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170 <P>Transformation, Data Enrichment, Source Lookup, Oracle, ROWID</P> Tue, 04 Jun 2024 12:52:04 GMT https://community.qlik.com/t5/Official-Support-Articles/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170 Michael_Litz 2024-06-04T12:52:04Z https://community.qlik.com/t5/Official-Support-Articles/Transformation-Source-Lookup-Oracle-ROWID/tac-p/2509570#M15523 <P>I was told that Qlik Replicate does not support custom queries joining two tables. However, can a source lookup be used joining two tables applying a filter condition.</P><P>Thank you,</P><P>Pranathi</P> Wed, 12 Mar 2025 16:04:40 GMT https://community.qlik.com/t5/Official-Support-Articles/Transformation-Source-Lookup-Oracle-ROWID/tac-p/2509570#M15523 unay 2025-03-12T16:04:40Z