Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to replicate my Oracle address table to a source Oracle address table, but only for the active clients.
How would i set the filter ? I have tried :
source_lookup('NO_CACHING','x','y','AD.ROW_ID
FROM SIEBEL.S_ADDR_PER AD, SIEBEL.S_ORG_GROUP O, SIEBEL.S_CONTACT C
WHERE O.PR_ADDR_PER_ID = AD.ROW_ID
AND O.ROW_ID = C.PR_GRP_OU_ID
AND C.CUST_STAT_CD = "Client actif" AND AD.ROW_ID=: --', 'z', $ROW_ID)
The tables are correctly joined so the problems lie in the dummy parameters that I'm using?
The errors are:
00049967: 2023-07-19T14:23:52 [SOURCE_CAPTURE ]E: ORA-01745: invalid host/bind variable name [1022307] (oracle_endpoint_unload.c:1445)
00049967: 2023-07-19T14:23:52 [SOURCE_CAPTURE ]E: Error in executing data lookup statement 'SELECT AD.ROW_ID
FROM SIEBEL.S_ADDR_PER AD, SIEBEL.S_ORG_GROUP O, SIEBEL.S_CONTACT C
WHERE O.PR_ADDR_PER_ID = AD.ROW_ID
AND O.ROW_ID = C.PR_GRP_OU_ID
AND C.CUST_STAT_CD = "Client actif" AND AD.ROW_ID=: -- FROM "x"."y" WHERE z' [1022307] (oracle_endpoint_unload.c:1448)
I have followed the example mentioned below but I run into syntax issues each time..
https://community.qlik.com/t5/Qlik-Replicate/How-can-we-Join-of-two-tables-using-Source-Lookup-with-...
Would it be possible to use this sql for both CDC and Full Load tasks ?
[edited - to add single quote escape method]
Please note that you hare asking about what is strictly a REPLICATE question, in the ENTERPRISE MANAGER FORUM. That forum is frequented by some Replicate users, but not many I suspect. Please consider jumping over to the appropriate REPLICATE forum where you will get a (10x? ) larger audience.
Let's first all realize that the (source|target)_lookup functions are designed for simple singleton queries which Replicate constructs on the fly from column, table, conditional parts. What is we are doing here is essentially SQL injection hijacking the return value (column) expression to stuff it with the entire query and making the Replicate provide remainder a mere comment. (TTL,'SCHM','TBL','EXP','COND'). This is similar to the provided example.
source_lookup('NO_CACHING','x','y',' ''quote test'' from dual --', 'z')
Worked for me. You try! ... look in the error message for result (if it fails) and report back please. To 'see' the result I had to made it fail deliberately providing a nonexistent table xxx and my deliberate error was: "\ Failed to get data lookup statement for table x.y \ Error in executing data lookup statement 'SELECT 'quote test' from XXX -- FROM "x"."y" WHERE z' \ ORA-00942: table or view does not exist "for example instead of:
'... AD.ROW_ID=:1 --', 'z', $ROW_ID)
you could try:
'... AD.ROW_ID= ''' || $ROW_ID || ''' --', 'z')
or with numeric ROW_ID:
'... AD.ROW_ID= ' || $ROW_ID || ' --', 'z')
>> Would it be possible to use this sql for both CDC and Full Load tasks ?
Not until you fix it. :-). Then it will work for full-load and cdc as all transformations do always unless they have additional magic to distinguish between the two (like looking at AR_H_TRANSACTION_ID)
Good luck. Let us know? Hein.
[edited - to add single quote escape method]
Please note that you hare asking about what is strictly a REPLICATE question, in the ENTERPRISE MANAGER FORUM. That forum is frequented by some Replicate users, but not many I suspect. Please consider jumping over to the appropriate REPLICATE forum where you will get a (10x? ) larger audience.
Let's first all realize that the (source|target)_lookup functions are designed for simple singleton queries which Replicate constructs on the fly from column, table, conditional parts. What is we are doing here is essentially SQL injection hijacking the return value (column) expression to stuff it with the entire query and making the Replicate provide remainder a mere comment. (TTL,'SCHM','TBL','EXP','COND'). This is similar to the provided example.
source_lookup('NO_CACHING','x','y',' ''quote test'' from dual --', 'z')
Worked for me. You try! ... look in the error message for result (if it fails) and report back please. To 'see' the result I had to made it fail deliberately providing a nonexistent table xxx and my deliberate error was: "\ Failed to get data lookup statement for table x.y \ Error in executing data lookup statement 'SELECT 'quote test' from XXX -- FROM "x"."y" WHERE z' \ ORA-00942: table or view does not exist "for example instead of:
'... AD.ROW_ID=:1 --', 'z', $ROW_ID)
you could try:
'... AD.ROW_ID= ''' || $ROW_ID || ''' --', 'z')
or with numeric ROW_ID:
'... AD.ROW_ID= ' || $ROW_ID || ' --', 'z')
>> Would it be possible to use this sql for both CDC and Full Load tasks ?
Not until you fix it. :-). Then it will work for full-load and cdc as all transformations do always unless they have additional magic to distinguish between the two (like looking at AR_H_TRANSACTION_ID)
Good luck. Let us know? Hein.
Hi @Heinvandenheuvel ,
This works like a charm, thank you. For those asking the same question, I'm including our implementation :
source_lookup('NO_CACHING','SIEBEL','S_ADDR_PER',' AP.ROW_ID from
SIEBEL.S_CONTACT C,
SIEBEL.S_CON_ADDR a , SIEBEL.S_ADDR_PER ap
where ap.ROW_ID = a.ADDR_PER_ID
and C.PR_GRP_OU_ID =a.ORG_GROUP_ID
and c.Cust_stat_cd=''Client actif'' AND AP.ROW_ID=''' || $ROW_ID || ''' --' ,'z')
groetjes,
Ole Dufour