Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
On my Source table from Oracle, I am using the source lookup function under Data Enrichment in order to retrieve the Oracle ROWID field value. 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 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.
The general form of the function is as follows:
| source_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS) |
For this example in am doing the lookup into the Employees table:
| source_lookup('NO_CACHING','HR','EMPLOYEES','ROWID','EMPLOYEE_ID=:1',$EMPLOYEE_ID) |
NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
HR is the schema in oracle
EMPLOYEES is the table
ROWID is what I want returned
EMPLOYEE_ID=:1 is the predicate for the lookup
$EMPLOYEE_ID is the value from the redo log that we are using in the predicate in place of :1
NOTE: If the source lookup needed more than one field in the key then:
(Multiple variables would look like: 'EMPLOYEE_ID=:1 AND DEPARTMENT_ID=:2)
$EMPLOYEE_ID, $DEPARTMENT_ID (in this example we are using field values that the task has read from the redo log file.
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. Please also note that the Oracle ROWID is not guaranteed to persist. Traditional Oracle ROWID's can change if a table is quiesced or rebuilt with dbms_redefinition.
This expression is created for the Add Column transformation ROWID as seen in the screen shot below.
NOTE: 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.
The screen shot below shows the target table with the ROWID field after the task has run.
Related Content
