Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Ole_Dufour
Contributor III
Contributor III

How to filter using sql in source_lookup

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 ?

Labels (2)
1 Solution

Accepted Solutions
Heinvandenheuvel
Specialist II
Specialist II

[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.

  • The referenced example appears to be SQL Server where arguments substitution points are indicated with a question mark(s). The need here is for Oracle syntax which as per Replicate Userguide requires a colon followed by a number. That number appears to be missing.
  • The desired query seems to want to include a piece of string "Client actif" as literal comparison test value. Fine. However, as per error message line that text come over as a double-quoted string which in Oracle SQL is interpreted as a column name is it not?
  • To provide a fixed literal you somehow have add a single quotes to the whole single quoted expression . That does not appear to be documented, best i checked. I tested using a double-single quote  and the transformation: 
     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 "
  • You could also try to provide that  'Client actif' as a (fixed) condition-parameter argument string and provide it with :1 or :2 in the expression as a first or second parameter.
  • Probably not this time, but sometimes, you need build the expression by  'gluing'  morsels together. 
    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.

 

 

 

View solution in original post

2 Replies
Heinvandenheuvel
Specialist II
Specialist II

[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.

  • The referenced example appears to be SQL Server where arguments substitution points are indicated with a question mark(s). The need here is for Oracle syntax which as per Replicate Userguide requires a colon followed by a number. That number appears to be missing.
  • The desired query seems to want to include a piece of string "Client actif" as literal comparison test value. Fine. However, as per error message line that text come over as a double-quoted string which in Oracle SQL is interpreted as a column name is it not?
  • To provide a fixed literal you somehow have add a single quotes to the whole single quoted expression . That does not appear to be documented, best i checked. I tested using a double-single quote  and the transformation: 
     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 "
  • You could also try to provide that  'Client actif' as a (fixed) condition-parameter argument string and provide it with :1 or :2 in the expression as a first or second parameter.
  • Probably not this time, but sometimes, you need build the expression by  'gluing'  morsels together. 
    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.

 

 

 

Ole_Dufour
Contributor III
Contributor III
Author

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